How do I SELECT Groups of records containing or Not containing certain Data

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)

How do I SELECT Groups of records containing or Not containing certain Data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-14 : 07:55:51

Tazman writes "How do I SELECT Groups of records containing or Not containing certain Data?

Example:

Device_ID Part_Description
--------- ----------------
2100 Hex Head
2100 Flat Head
2100 Round Head
2100 Square Head
2200 Triangle Head
2200 Flat Head
2200 Square Head
2200 Round Head
2856 Flat Head
2856 Round Head
2856 Square Head

I have this table and I am trying to select all the Device_ID's
in this table that "Do Not" include Part_Description='Triangle Head'. So after my query, I would have returned to me:

2100 - Hex Head
2100 - .
2100 - .
2100 - .
2856 - .
2856 - .
2856 - Square Head

Thanks in advance..
-Scott"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-14 : 08:16:51

Not sure want you want

select * from tbl t1 where not exists (select * from tbl t2 where t1.Device_ID = t2.Device_ID and t2.Part_Description = 'Triangle Head')

or maybe

select * from tbl where Device_ID not in (select distinct Device_ID from tbl where Part_Description = 'Triangle Head')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-14 : 08:52:11

my preference (and a good way in my opinion to anaylze and sovle problems like this) goes like this:

first, we need a query to return all Device_ID's in which there is a triangle head:


Query1:

select distinct Device_ID from tbl where part_Description='Triangle Head'

Now, using a LEFT OUTER JOIN, select from the main table, outer joining to the above query:


Select tbl.*
from 
tbl
left outer join (query1) a
on a.device_ID = tbl1.device_id
WHERE a.Device_ID is NULL

the where clause ensures only rows are returned from the original table in which that row's ID has parts of that description.

Just another way of thinking of the problem. not sure which is more efficient or easier to understand for you, but I prefer "breaking up things into smaller parts" techniques whenever possible. (I'm a pretty simple guy !)

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-01-14 : 09:01:15

To expand a little on Mr. Cross Join's fine solution, this is also possible with the join:

SELECT	a.*
FROM	tbl a LEFT OUTER JOIN tbl b --same table self-joined
	ON  a.device_ID = b.device_id
	AND b.part_Description='Triangle Head'
WHERE	a.Device_ID is NULL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-14 : 10:09:30

Just be careful of that technique -- if more than one part has the same part description of "Triangle Head", you will return duplicated rows from the main table due to the join.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-14 : 11:30:04

quote:
Originally posted by jsmith8858

Just be careful of that technique -- if more than one part has the same part description of "Triangle Head", you will return duplicated rows from the main table due to the join.
- Jeff

It's not returning the rows that join with "Triangle Head" so won't have a duplicate problem.
(well if the where clause was b.Device_ID is NULL it wouldn't - it will never return anything as it is).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-14 : 14:47:20

yeah ... i think you're right ... don't know why but that one is really hard to think about ... gives me a headache!

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-14 : 19:40:26

a minor correction of drymmie's select: