I'm using a subquery, but I don't want to

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

I'm using a subquery, but I don't want to

Author

Topic

JarWithLid
Starting Member

10 Posts

Posted - 2007-12-19 : 14:44:09

Tables:

|product_families
|----------------------
|id
|name

|samptask_pfam_j (sample_task/product_families/join)
|----------------------
|sample_task_id
|product_family_id

Given a sample task id, I need to retrieve a list of all of the product families with a boolean field denoting whether or not the sample task is joined to that product family. Right now I am doing this:

--------------------------------------------


SELECT pf.id, pf.name,
       CASE
       WHEN (
               SELECT 1
               FROM samptask_pfam_j
               WHERE sample_task_id = @SAMPLETASKID
               AND product_family_id = pf.id
             ) 
        IS NULL THEN 0
        ELSE 1
        END AS joined
FROM    product_families pf

--------------------------------------------

I thought I could do something like this...

--------------------------------------------


SELECT    pf.id, pf.name, 
          CASE WHEN spj.sample_task_id IS NULL THEN 0
          ELSE 1
          END AS joined
FROM      product_families pf
LEFT JOIN samptask_pfam_j spj
       ON spj.product_family_id = pf.id
WHERE     spj.sample_task_id = @SAMPLETASKID
    OR    spj.sample_task_id IS NULL

--------------------------------------------

But it does not work. Suggestions?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-19 : 14:58:28

Never say "it does not work" -- you need to explain why and what happens and why it is wrong and what you expect.

I will take a guess, though. Try:


SELECT    pf.id, pf.name, 
          CASE WHEN spj.sample_task_id IS NULL THEN 0
          ELSE 1
          END AS joined
FROM      product_families pf
LEFT JOIN samptask_pfam_j spj
       ON spj.product_family_id = pf.id and 
          spj.sample_task_id = @SAMPLETASKID

more here:

http://www.sqlteam.com/item.asp?ItemID=11122
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-19 : 15:00:20

Try this:

quote:

Originally posted by JarWithLid


SELECT    pf.id, pf.name, 
          CASE WHEN spj.sample_task_id IS NULL THEN 0
          ELSE 1
          END AS joined
FROM      product_families pf
LEFT JOIN samptask_pfam_j spj
       ON spj.product_family_id = pf.id
       AND spj.sample_task_id = @SAMPLETASKID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-19 : 15:08:01

beat you by about 50 seconds!

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:37:52

Or use EXISTS if the LEFT JOIN produces unwanted duplicate records

SELECT	pf.id,
	pf.name,
	CASE
		WHEN EXISTS (SELECT * FROM SampTask_Pfam_AS j WHERE j.sample_task_id = @SAMPLETASKID AND j.product_family_id = pf.id) THEN 1 
		ELSE 0
        END AS Joined
FROM	Product_Families AS pf

E 12°55'05.25"
N 56°04'39.16"

JarWithLid
Starting Member

10 Posts

Posted - 2007-12-19 : 16:00:59

Ok... so does that return different results the second query in my original post?

What's funny is that now, when I run that (second) query, it returns the correct results, same as everyone's suggestions. I swear, before, it wasn't returning unmatched rows from the second table, as if I was doing an inner join instead of an outer...

EDIT - that was regarding all suggestions minus that of the one using "exists" (which is clearly a different method than my second, original query)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources