trembling trembling - 7 months ago 10
SQL Question

find all values 'x' that do not contain a 'y'

I have to provide a list of all active

site
s that do not contain have a
domain


SELECT companies.name,sites.name,sites.association
FROM companies JOIN sites ON sites.company = companies.id
JOIN domains ON domains.site != sites.id WHERE sites.is_deleted = 0
AND domains.is_primary = 1 AND domains.is_deleted = 0


I am joining on the condition that the primary key for
sites
does not correspond with anything in the
domains.site
field. However, I know I am not getting the output I need since I know the number or rows I want and I am generating more than that.

The uml diagram for te schema I am working with is here (it's a little funky).

Answer

this might give you what you need assuming you're trying to find sites that do not have a primary domain. it uses left join and checks to see if the domain record is null/missing

SELECT  companies.name,
        sites.name,
        sites.association
FROM    companies
        JOIN sites ON sites.company = companies.id
        LEFT JOIN domains ON domains.site = sites.id
                             AND domains.is_primary = 1
                             AND domains.is_deleted = 0
WHERE   sites.is_deleted = 0
        AND domains.id IS NULL

You can also consider using NOT EXISTS

SELECT  companies.name,
        sites.name,
        sites.association
FROM    companies
        JOIN sites ON sites.company = companies.id
WHERE   sites.is_deleted = 0
        AND NOT EXISTS (
            SELECT 1
            FROM    domains d
            WHERE   d.site = sites.id
                    AND d.is_primary = 1
                    AND d.is_deleted = 0
        )

here is a sql fiddle for you to test SQL FIDDLE