CODEWITHSUNDEEP
sqlsql-server
Unbreakable
Unbreakable

Reputation: 8132

Convert a subquery to a query using Join

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

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

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

Sahi
Sahi

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

DatabaseCoder
DatabaseCoder

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

Related Questions