Select rows based on grouped values in another tbl

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)

Select rows based on grouped values in another tbl

Author

Topic

aiccucs
Starting Member

7 Posts

Posted - 2012-11-02 : 10:44:14

I'll try and explain this the best I can. I think the example will be the most helpful.

I'm trying to select all rows from one table when the joining table has a column that isn't NULL. I'm having difficulty because its based on a group and I need to grab the rows when all of the values in the 2nd table aren't NULL based on the group its in.

In the example below I want to get row #3 and #4 in my results.

So a.Group '2' has two IDs that both have a b.DATE field that isn't NULL.

a.Index	a.Group	a.ID	b.ID	b.Date
--------------------------------------
1	1	10	10	1/1/12
2	1	20	20	NULL
3	2	30	30	1/1/12
4	2	40	40	1/1/12
5	3	50	50	NULL
6	3	60	60	NULL

a.ID = b.ID

If I'm not making any sense, please let me know.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 10:48:14

[code]SELECT *
FROM A
WHERE NOT EXISTS
(
SELECT *
FROM B
WHERE b.ID = a.ID
AND b.Date IS NULL
);[/code]

aiccucs
Starting Member

7 Posts

Posted - 2012-11-02 : 11:07:36

With that query I still get a.Group '1' in my results since it has one row in table B which has a value in b.Date.

I only want to see the rows in table A where all b.Date values aren't NULL. So a.Group '1' and '3' should be excluded from the results completely.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 11:25:29

Sorry about that - I missed that. The following query should work, although I think there are simpler ways to do this

;WITH cte AS
(
	SELECT a.*,b.Date FROM a INNER JOIN b ON a.id = b.id
)
SELECT * FROM cte a
WHERE NOT EXISTS 
(
	SELECT * FROM cte b
	WHERE b.group = a.group
	AND b.Date IS NULL
)

aiccucs
Starting Member

7 Posts

Posted - 2012-11-02 : 11:52:53

For the second part of the query...

	SELECT * FROM cte b
	WHERE b.group = a.group
	AND b.Date IS NULL

b.group = a.group won't work as b.group doesn't exist.

When I tried it with b.ID = a.ID I got the same results as the first query.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 11:57:47

It is probably getting confused because I used the alias a.

;WITH cte AS
(
	SELECT a.*,b.Date FROM a INNER JOIN b ON a.id = b.id
)
SELECT * FROM cte x
WHERE NOT EXISTS 
(
	SELECT * FROM cte y
	WHERE x.group = y.group
	AND y.Date IS NULL
)

aiccucs
Starting Member

7 Posts

Posted - 2012-11-02 : 12:41:19

That worked perfectly, thank you very much. Now I'm off to research on what this is actually doing. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources