Return result if attribute is not in table

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 2008 Forums

Transact-SQL (2008)

Return result if attribute is not in table

Author

Topic

spunkiegirl
Starting Member

9 Posts

Posted - 2014-04-13 : 19:21:16

Good Afternoon,

I have a table named CallNbr with one record per Call. I have another table CallAttributes with labor, travel and parts for each Call.

So if I have CallNbr 12345, CallAttributes would look like the below.

12345 Labor L 45.00
12345 Travel T 30.00
12345 Part P 10.00

I have another CallNbr 13333, CallAttributes look like the below, it has no parts.

13333 Labor L 45.00
13333 Travel T 30.00

I need a sql script that loops through the CallAttribute table and only returns the CallNbr of records that do not have an entry for Part, so my first example, Call 12345, would not be returned in my result set because it has a Part in the CallAttributes table. My second example, call 13333 would be returned, because there is no Part in the CallAttributes table.

Thank you so much!

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-13 : 20:48:00

Do you mean following?

SELECT
	[Call]
FROM CallNbr
WHERE NOT EXISTS(
	SELECT *
	FROM CallAttributes T
	WHERE T.Column2 = 'Part'
	AND T.Column1 = CallNbr.[Call]
)

SELECT DISTINCT
	Column1
FROM CallAttributes
WHERE NOT EXISTS(
	SELECT *
	FROM CallAttributes T
	WHERE T.Column2 = 'Part'
	AND T.Column1 = CallAttributes.Column1
)

-------------------------------------
From Japan
Sorry, my English ability is limited.

spunkiegirl
Starting Member

9 Posts

Posted - 2014-04-13 : 21:47:26

Thank you so much! The first one worked exactly like I needed!
Thank you, Thank you, Thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources