Reputation: 8132
I have two tables
tblTenant
idTenant Name
1 Hello
2 World
3 Foo
4 Bar
tblPhone
idTenant idPhoneType PhoneNum
1 23 31445
1 24 43123
1 25 90899
2 23 90937
2 24 34544
4 24 23455
Now I want all the tenants Id's who never have a phonetype of 25.
Output:
idTenant
2
3
4
idTenant = 1 is excluded because it has an entry of phonetype = 25
Query I wrote:
select * from tblTenant where idTenant not in ( SELECT distinct(idTenant) FROM tblPhone where idPhoneType = 25) ;
But I want to write this query using JOINS. Is it possible? Please guide me.
.
Upvotes: 0
Views: 81
Reputation: 522817
Use conditional aggregation:
SELECT
t1.idTenant, t1.Name
FROM tblTenant
LEFT JOIN tblPhone t2
ON t1.idTenant = t2.idTenant
GROUP BY
t1.idTenant, t1.Name
HAVING
SUM(CASE WHEN t2.idPhoneType = 25 THEN 1 ELSE 0 END) = 0 AND
COUNT(t2.idTenant) > 0;
The crux of the above query is that we aggregate over the tblPhone
by tenant, and assert that phone type 25 never occurs. Then, we join to tblTenant
to bring in the actual tenant name.
Upvotes: 3
Reputation: 1484
try this:
CREAte TAble #tblTenant(idTenant INT,Name VARCHAR(10))
INSERT INTO #tblTenant VALUES(1,'Hello')
INSERT INTO #tblTenant VALUES(2,'World')
INSERT INTO #tblTenant VALUES(3,'Foo')
INSERT INTO #tblTenant VALUES(4,'Bar')
--SELECT * from #tblTenant
CREATE TABLE #tblPhone(idTenant INT,idPhoneType INT,PhoneNum BIGINT)
INSERT INTO #tblPhone vALUES(1,23,31445)
INSERT INTO #tblPhone vALUES(1,24,43123)
INSERT INTO #tblPhone vALUES(1,25,90899)
INSERT INTO #tblPhone vALUES(2,23,90937)
INSERT INTO #tblPhone vALUES(2,24,34544)
INSERT INTO #tblPhone vALUES(4,4,23455)
--select * from #tblPhone
select t.idTenant from #tblTenant t
LEFT JOIN #tblPhone p on t.idTenant=p.idTenant and p.idPhoneType=25
WHERE p.idTenant IS NULL
DROP TABLE #tblPhone
DROP TABLE #tblTenant
Upvotes: 1
Reputation: 2032
We can use Left Join
for this -
select distinct
T.idTenant
from tblTenant T
left join tblPhone P on P.idTenant = T.idTenant and P.idPhoneType = 25
where P.idTenant IS NULL
Upvotes: 1