CODEWITHSUNDEEP
mysqlsql
Nick
Nick

Reputation: 33

SQL Join and exclude / filter

I've got an SQL query which joins 2 tables, I'm trying to filter rows which match a condition, then filter out the results if the same condition matches with different values, but the last WHERE clause seems to be ignored:

SELECT DISTINCT
    tblclients.firstname, tblclients.lastname, tblclients.email
FROM
    tblclients
LEFT JOIN
    (
        SELECT *
        FROM tblhosting
        WHERE tblhosting.packageid IN (75,86)
    ) tblhosting ON tblclients.id = tblhosting.userid
WHERE 
    tblhosting.packageid NOT IN (76,77,78)

The idea being to get a list of customers which have a certain package (ID 75 and 86), then exclude/take out any results/customers which also have another package as well as 75/86 (ID 76,77,78 etc). It's not excluding those results though at all, tried numerous variations here on Stackoverflow, where am I going wrong please?

Upvotes: 3

Views: 1787

Answers (3)

sstan
sstan

Reputation: 36483

I wouldn't use a LEFT JOIN at all in this case. I would use a combination of EXISTS and NOT EXISTS to get your desired results.

SELECT DISTINCT c.firstname, c.lastname, c.email
  FROM tblclients c
 WHERE EXISTS (SELECT *
                 FROM tblhosting h
                WHERE h.userid = c.id
                  AND h.packageid IN (75,86))
   AND NOT EXISTS (SELECT *
                     FROM tblhosting h
                    WHERE h.userid = c.id
                      AND h.packageid IN (76,77,78))

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93704

If am not wrong this is what you are looking for

SELECT tblclients.firstname, tblclients.lastname, tblclients.email
FROM
    tblclients
LEFT JOIN
    (
     SELECT userid
        FROM tblhosting
        WHERE tblhosting.packageid IN (75,86,76,77,78)
    group by  userid
   having count(1) = count(case when packageid IN (75,86) then 1 end)
) tblhosting  
ON tblclients.id = tblhosting.userid

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Add it to the join condition itself. When you have a where clause filter your join would be treated as inner join.

tblhosting ON tblclients.id = tblhosting.userid and tblhosting.packageid NOT IN (76,77,78)

Upvotes: 4

Related Questions