Selecting only records where both of two values exist (2 Viewers)

[plog]

With those database constraints I was wrong. Cheeky's solution will work. Sorry.

 

[GPGeorge]

SELECT    c.*
FROM     Contacts c
WHERE EXISTS (SELECT 'x'
                FROM tblContact_Categories cc1  
                INNER JOIN tblCategoryTypes t
                    ON cc1.CategoryTypeID = t.CategoryTypeID
                WHERE t.[Category Type]  = 'car'
                AND cc1.ContactID = c.ContactID)
AND EXISTS (SELECT 'x'
                FROM tblContact_Categories cc1  
                INNER JOIN tblCategoryTypes t
                    ON cc1.CategoryTypeID = t.CategoryTypeID
                WHERE t.[Category Type]  = 'bike'
                AND cc1.ContactID = c.ContactID)

Winner.

 

[Ken Sheridan]

Winner.

Rather than calling the EXISTS predicate and multiple subqueries, which could be tedious if the value list were a long one, the attached little demo file illustrates how rows can be returned in the context of a binary relationship by selecting values in a multi-select list box. In the demo this is done by filtering a report's output by means of the following criterion:

strCriteria = "StoreID IN(SELECT StoreID " & _
    "FROM StoreStock " & _
    "WHERE StockID IN(" & strStockIDList & ") " & _
    "GROUP BY StoreID " & _
    "HAVING COUNT(*) = " & ctrl.ItemsSelected.Count & ")"

To do the same thing completely in a query would be:

SELECT
    Store,
    Stock
FROM
    Stocks
    INNER JOIN (
        Stores
        INNER JOIN StoreStock ON Stores.StoreID = StoreStock.StoreID
    ) ON Stocks.StockID = StoreStock.StockID
WHERE
    Stores.StoreID IN (
        SELECT
            StoreID
        FROM
            StoreStock
            INNER JOIN Stocks ON Storestock.StockID = Stocks.StockID
        WHERE
            Stock IN ("Apples", "Oranges")
        GROUP BY
            StoreID
        HAVING
            COUNT(*) = (
                SELECT
                    COUNT(*)
                FROM
                    Stocks
                WHERE
                    Stock IN ("Apples", "Oranges")
            )
    );

This is really just an example of relational division, which was one of Codd's original algebraic operators when he first proposed the database relational model back in 1970. The usual published examples of this generally divide one table into another, but the principle is much the same with a value list.

 

[John O'Malvern]

No, for GDPR reasons.

 

[June7]

That's why one would make copy and remove sensitive data.