April 11, 2012 at 12:25 pm
table name:
table: product_to_cat
product_id Cat_id
501 69
502 30
503 69
503 30
504 69
504 69
505 69
506 69
507 69
507 30
I need to get product_id if the the (cat_id=69 and 30)...I.e only 503, 507....
Thanks in advance.
Prabakar
April 11, 2012 at 12:28 pm
you'll have to join the table agaisnt itself wiht an alias:
something like this:
select * from product_to_cat t1
left outer join product_to_cat t2
on t1.product-id = t2.product_id
where t1.cat_id = 69
and t2.catid = 30
cuteprabakar (4/11/2012)
table name:table: product_to_cat
product_id Cat_id
501 69
502 30
503 69
503 30
504 69
504 69
505 69
506 69
507 69
507 30
I need to get product_id if the the (cat_id=69 and 30)...I.e only 503, 507....
Thanks in advance.
Prabakar
Lowell
April 11, 2012 at 12:32 pm
Something like this?
SELECT product_id
FROM <YOutable>
WHERE cat_id = 69
INTERSECT
SELECT product_id
FROM <YOutable>
WHERE cat_id = 30
Please read this artcile on multiple ways to do that
http://www.sqlservercentral.com/articles/T-SQL/88244/
The code i showed was teh fastest perfoming accr to that article (check "Join the discussion" link in that article ) for multiple solutions
April 11, 2012 at 1:02 pm
Thank you
April 18, 2012 at 2:59 am
Why would you JOIN a table onto itself unless you have to? I've never found this approach to be particularly efficient when you have 1,000,000+ rows in your table.
Take a look at this solution:
DECLARE @prd_to_cat TABLE (product_id INT, Cat_id INT)
INSERT INTO @prd_to_cat (product_id,Cat_id)
SELECT 501,69
UNION ALL SELECT 502,30
UNION ALL SELECT 503,69
UNION ALL SELECT 503,30
UNION ALL SELECT 504,69
UNION ALL SELECT 504,69
UNION ALL SELECT 505,69
UNION ALL SELECT 506,69
UNION ALL SELECT 507,69
UNION ALL SELECT 507,30
-- Solution posted by Lowell with a couple of corrections to make it run
select t1.product_id from @prd_to_cat t1
left outer join @prd_to_cat t2
on t1.product_id = t2.product_id
where t1.cat_id = 69
and t2.cat_id = 30
-- Alternate that doesn't join the table on itself
;WITH prd AS (
SELECT product_id, cat_id
,CASE WHEN cat_id = 30 THEN 1 ELSE 0 END As Count30
,CASE WHEN cat_id = 69 THEN 1 ELSE 0 END As Count69
FROM @prd_to_cat)
SELECT product_id
FROM prd
GROUP BY product_id
HAVING SUM(Count30) = 1 and SUM(Count69) = 1
Before you run it, take a look at the query plan cost of both versions.
You may want to change the =1 to >=1, depending on whether you might have more than one cat_id = 30 or cat_id = 69.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 3:03 am
ColdCoffee's INTERSECT solution is neat but still not the winner when execution plan costs are compared.
I get: Lowell (36%)/ColdCoffee (27%)/Dwain (22%)/initial INSERT (15%)
Just remember that query plan cost doesn't tell the whole story... try it on a large set of test data to be sure.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 3:24 am
Here's an even better one according to query plan cost:
SELECT product_id
FROM @prd_to_cat p2
WHERE EXISTS (
SELECT product_id
FROM @prd_to_cat p1
WHERE p1.product_id = p2.product_id and p1.cat_id = 30)
and p2.cat_id = 69
Never rest until you've found the ultimate solution!
BTW. This one surprised the heck out of me. Like the other solutions (except my original), it does 2 table scans. But probably wins the race because EXISTS doesn't cause a full scan.
Maybe a true expert on reading the execution plans could shed some light on this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply