SQL: No Sub-Query but only Joins

Question

I am trying to find an alternative to this query where no sub-select is present but only joins (left, right, inner etc).

select count(SC.NUMBER_C) 
from FNQP2PORTAL.SA.SEC_CAS SC
where SC.NUMBER_C not in (
select E.Reference from 
FNQP2HQ.dbo.Entities E
join FNQP2HQ.dbo.Classes C on C.ID = E.ClassID 
where C.Reference =  'Assembly case')

So, it should be something like...

select count(SC.NUMBER_C)
from FNQP2PORTAL.SA.SEC_CAS SC
left join  FNQP2HQ.dbo.Entities E on E.Reference = SC.NUMBER_C and E.Reference IS NULL
inner join FNQP2HQ.dbo.Classes C on C.ID = E.ClassID and C.Reference =  'Assembly case'

Can somebody help me here?

Answer 1

The queries you posted are very different. One is inclusive (where SC.NUMBER_C IN.... - and the other is exclusive (left outer join where E.Reference is NULL). Because of these completely different queries, it's unclear what you're trying to do.

This is how you would accomplish your first query with a join instead of the subquery.

SELECT COUNT(SC.NUMBER_C) 
FROM FNQP2PORTAL.SA.SEC_CAS SC
JOIN FNQP2HQ.dbo.Entities E
    ON SC.NUMBER_C = E.Reference
JOIN FNQP2HQ.dbo.Classes C 
    ON C.ID = E.ClassID 
WHERE C.Reference = 'Assembly Case'

EDIT:

You need to use a subquery and NOT IN to get what you're asking for in your comment. You can't only use joins.

SELECT COUNT(SC.NUMBER_C) 
FROM FNQP2PORTAL.SA.SEC_CAS SC
WHERE SC.NUMBER_C NOT IN 
    (
        SELECT E.Reference 
        FROM FNQP2HQ.dbo.Entities E
        JOIN FNQP2HQ.dbo.Classes C 
            ON C.ID = E.ClassID 
        WHERE C.Reference = 'Assembly Case'
    )