CODEWITHSUNDEEP
sqlms-access
cherrie
cherrie

Reputation: 75

How to query for non-matching records

I would like to create a query that finds those members with no reviews.

Table 1 - items selected
member/audno
733 12
733 13
733 14
844 12
844 13
844 14
955 12
955 13
955 14

Table 2 - reviews
member/audno/reviewno
733 12 111
844 13 112
955 14 113

Create new query with results of members with items with no reviews: member/audno
733 13
733 14
844 12
844 14
955 12
955 13

How can I do this?

Upvotes: 0

Views: 42

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112382

Try this

SELECT *
FROM
    items_selected I
WHERE
    NOT EXISTS (
        SELECT *
        FROM
            reviews R
        WHERE
            R.member = I.member AND R.audno = I.audno
    )

Another, equivalent query is based on a left join

SELECT I.*
FROM
    items_selected I
    LEFT JOIN reviews R
        ON I.member = R.member AND I.audno = R.audno
WHERE
    R.member IS NULL

You might test both of them and look which one is more performant.

Upvotes: 1

Related Questions