Joining 2 result sets - 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)

Joining 2 result sets

Author

Topic

allycavs
Starting Member

2 Posts

Posted - 2010-07-07 : 12:11:21

Hi

Suppose I have a query like so


SELECT ID 
FROM indicator_Table
Where field2=100

This will return 1,2,3,4

I then want a second query that effectively looks like this


Select DISTINCT group_id
From groupTable
Where ind_id=1 --resultset from above row 1
AND   ind_id=2 --resultset from above row 2
AND   ind_id=3 --resultset from above row 3
AND   ind_id=4 --resultset from above row 4

groupTable looks like:


id   Group_id     ind_id
1     1           1
2     1           2
3     1           3
4     1           4
5     2           3
6     2           4
7     3           ....

The above result is of course 1!!
The first select will always have a variable number instead of 100 so a having that select as a cursor and looping through is not my answer. I dont need the exact same queries but same effect. Any help is much appreciated

Thanks
Alan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 12:13:46

ind_id cannot be 1 AND 2 AND 3 AND 4
Do you mean 1 OR 2 OR 3 OR 4 ?

No, you're never too old to Yak'n'Roll if you're too young to die.

allycavs
Starting Member

2 Posts

Posted - 2010-07-08 : 03:38:43

sorry the second query would like more like this actually


Select DISTINCT group_id
From groupTable
Where group_id IN (Select group_id 
                   From groupTable 
                   Where ind_id=1 )--resultset from above row 1

AND group_id IN (Select group_id 
                   From groupTable 
                   Where ind_id=2 )--resultset from above row 2


AND group_id IN (Select group_id 
                   From groupTable 
                   WHERE ind_id=3) --resultset from above row 3

AND group_id IN (Select group_id 
                   From groupTable 
                   WHERE iind_id=4) --resultset from above row 4

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-07-10 : 03:04:16

>> The above result is of course 1!! <<
I think you are looking for Relational Division.

>>The first select will always have a variable number instead of 100<<
No problem, you can use variable instead of constant expression.

Try it:


SELECT group_id
FROM   groupTable
GROUP  BY group_id
HAVING COUNT(DISTINCT CASE WHEN ind_id IN (1, 2, 3, 4) THNE ind_id ELSE NULL END) = 4 ;

Or the numbers are in the first query:


SELECT group_id
FROM   groupTable
WHERE  ind_id IN (SELECT SELECT ID 
                  FROM   indicator_Table
                  WHERE  field2 = 100)
GROUP  BY group_id
HAVING COUNT(DISTINCT ind_id) = (SELECT COUNT(*) 
                                 FROM   indicator_Table 
                                 WHERE  field = 100) ;

Or with JOIN and Aggregate Window Function:


SELECT group_id
FROM   groupTable AS g
       INNER JOIN 
       (SELECT COUNT(*) OVER() AS cnt, 
               ID
        FROM   indicator_Table
        WHERE  field = 100) AS i
ON      g.ind_id = i.ID
GROUP BY group_id
HAVING COUNT(DISTINCT ind_id) = MAX(cnt) ;

Or:


SELECT group_id
FROM
(
SELECT group_id, COUNT(DISTINCT ind_id) AS cnt
FROM   groupTable
WHERE  ind_id IN (SELECT ID FROM indicator_Table WHERE  field2 = 100)
GROUP  BY group_id
) D
JOIN
(
SELECT COUNT(*)
FROM   indicator_Table
WHERE  field2 = 100
) AS DD(cnt)
ON D.cnt = DD.cnt ;

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources