advanced query - SQL Server Forums

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

Transact-SQL (2005)

advanced query

Author

Topic

bcanonica
Starting Member

35 Posts

Posted - 2009-06-09 : 10:20:29

I need to return only the values from the below table that do not have a matching type of 1 in this join table listed below. My guess is with a self join on the table, but I am not sure how to NOT return the foreign keys that have a match of type 1. IDs 2 and 3 are disqualified, because they have a record with type 1 in the table. That leaves us with IDs 1 and 5 which are the only records that have records with only type of 2. You guys are always so smart on this board this will probably be an easy one for you, thanks in advance for any help.

TABLE

RESULTS

bcanonica
Starting Member

35 Posts

Posted - 2009-06-09 : 10:23:39

Sorry ID 1 is not disqualified, because it only has one record of type 2. 2, 3, and 4 are disqualified, because their foreignIDs has a record with type 1.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-09 : 10:40:46

[code]SELECT *
FROM YourTable T1
WHERE NOT EXISTS
(
SELECT *
FROM YourTable T2
WHERE T1.ForeignId = T2.ForeignId
AND T2.[Type] = 1
)

SELECT T1.*
FROM YourTable T1
LEFT JOIN YourTable T2
ON T1.ForeignId = T2.ForeignId
AND T2.[Type] = 1
WHERE T2.ForeignId IS NULL

SELECT *
FROM YourTable
WHERE ForeignId NOT IN
(
SELECT T2.ForeignId
FROM YourTable T2
WHERE T2.[Type] = 1
)
[/code]
etc

bcanonica
Starting Member

35 Posts

Posted - 2009-06-09 : 11:10:35

Very close. But that doesn't solve my requirements. I get to that point with the above queries, but I need to return the values where the ForeignID does NOT have a record with type 1, but does have a record of type 2.

The above queries return the foreign key records that do have a matching record that have type 1.

Thanks again.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-09 : 11:40:40

How about this?

Jim

SELECT a.* 
FROM
@table a
INNER JOIN

(
	select a.foreignid,[cnt] = count(*)
	from @table  a
	group by a.foreignid
	having count(*) = 1
) t1
ON
    a.foreignid = t1.foreignid
and a.[type]    = 2

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-09 : 11:44:05

[code]SELECT *
FROM YourTable
WHERE ForeignId IN
(
SELECT T1.ForeignId
FROM YourTable T1
GROUP BY T1.ForeignId
HAVING SUM(CASE WHEN T1.[Type] = 1 THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN T1.[Type] = 2 THEN 1 ELSE 0 END) > 0
)[/code]

etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:14:39

[code]SELECT t.*
FROM Table t
JOIN
(
SELECT t1.ID
FROM Table t1
JOIN Table t2
ON t2.ID=t1.ForeignID
GROUP BY t1.ID
HAVING SUM(CASE WHEN t1.TYPE = 1 OR t2.Type = 1 THEN 1 ELSE 0 END)>0
)tmp
ON tmp.ID=t.ID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources