SQLTeam.com | Weblogs | Forums

SQL Select Statement from another Query that is grouped

Transact-SQL
#1

Hi All,

I have 2 tables

Table1:
clientid
ref

Table2:
clientid

a)I want to first of all get all clients from table 1 that ref <>25 and then group those results by clientid
b)I want to now take the clients from the above query and match them with those in table2

Thanks

#2 
SELECT t1.clientid, t2...
FROM (
    SELECT clientid
	FROM Table1
	WHERE ref <> 25
	GROUP BY clientid
) AS t1
INNER JOIN Table2 t2 ON t2.clientid = t1.clientid
#3

Hi Scott thanks for your prompt reply. As the data is normalised I might have some records with the same clientid in table 1 that has a different ref. I want to exclude the client as a whole if they had a ref of 25. Example if a client 1234 has 2 entries and 1 entry was 25 I want that client ignored. So if the table had 5 entries like below only 2 will be shown.
Clientid 1234 Ref 25
Clientid 1234 Ref 22
Clientid 3456 Ref 33
Clientid 5555 Ref 25
Clientid 8899 Ref 27

I assume an idea is get all clients from table 1 that =25 and as part of the inner join ignore those ?

Thanks

#4 
SELECT t1.clientid --, t2...
FROM (
    SELECT clientid
	FROM Table1
	GROUP BY clientid
    HAVING MAX(CASE WHEN ref = 25 THEN 1 ELSE 0 END) = 0
) AS t1
INNER JOIN Table2 t2 ON t2.clientid = t1.clientid
1 Like
#5

Hi Scott I did this just before you posted yours. Is this correct also ?

..
Inner Join table t2 on t2.clientid = t1.clientid where t1.clientid not in (Select clientid from Table1 where ref = 25 group by clientid)

#6

Yes, that should work too.

1 Like
#7

Thanks you are a star :clap: :clap: