SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 how to combine these two queries?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

9 Posts

Posted - 02/18/2013 :  20:44:15  Show Profile  Reply with Quote
Hi there. I have to combine these two queries into one using a join:

SELECT categories.category_id, categories.title, categories.description,categories.parent_id,categories.display_weight,
categories.image_thumb, categories.deleted, categories.image_banner,
FROM categories 
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.

Very Important crosS Applying yaK Herder

52325 Posts

Posted - 02/19/2013 :  00:12:34  Show Profile  Reply with Quote

SELECT p.product_id, p.name, p.image_thumb, p.deleted ,
c.category_id, c.title, c.description,c.parent_id,c.display_weight,
c.image_thumb, c.deleted, c.image_banner
FROM product p
JOIN product_category pc
ON pc.product_id = p.product_id 
LEFT JOIN categories c
ON c.parent_id = ps.category_id
AND c.category_id <> ps.category_id
ORDER BY p.display_weight DESC

SQL Server MVP

Edited by - visakh16 on 02/19/2013 00:12:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000