If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > how to combine these queries into one using a join?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
Exclamation how to combine these queries into one using a join?

Hi there. I apologize but I'm a novice at sql...
I have to combine these two queries into one using a join:

SELECT categories.category_id, categories.title, categories.description,categories.parent_id,catego ries.display_weight,
categories.image_thumb, categories.deleted, categories.image_banner,
FROM categories
WHERE
AND parent_id=18
AND category_id <> 18

And here's the second one:


SELECT `product.product_id`, `product.name`, `product.image_thumb`, `product.deleted`
FROM (`product`)
WHERE `product_id` IN (select product_id from product_category where category_id=18)
ORDER BY `display_weight` DESC

A few points:

It's possible to have a scenario where the first query will return 0 rows, but the second does contain records.

Also, you'll notice that the second query has a subquery baked in. The relationship between the product table and category table is defined in the product_category table.
The product table on its own doesn't know which categories the products belong to.

Any help would be appreciated.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2008
Location: At work...
Posts: 84
A standard (INNER) JOIN will return 0 rows if one of the tables is empty. Check out about OUTER JOIN's instead. (I.e. LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN.)

How come query may return 0 rows, but the second does contain records?

How do the two queries relate to each other? (Join condition...)
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,227
Another option might be UNION(or UNION ALL) of those two queries by adding some dummy columns for each query, to make same select column list for two queries.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On