Menu
How would I write a SQL query that excludes a record if one (external) record from a one to many join matches a certain condition?
For example:
Details
ID
1
2
Items
DetailID Item
1 A
1 B
2 B
2 C
How would I select the detail records where the Items do not include 'A'?
372
To exclude multiple values to be fetched from a table we can use multiple OR statements but when we want to exclude a lot of values it becomes lengthy to write multiple AND statements, To avoid this we can use the NOT IN clause with the array of values that need to be excluded with the WHERE statement.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
When you use a simple (INNER) JOIN , you'll only get the rows that have matches in both tables. The query will not return unmatched rows in any shape or form. If this is not what you want, the solution is to use the LEFT JOIN , RIGHT JOIN , or FULL JOIN , depending on what you'd like to see.
Introduction to SQL EXCLUDE. In SQL, in order to EXCLUDE certain rows from being returned by a SELECT query, we use some restricting or excluding conditions based on some criteria. EXCLUDE conditions in SQL usually appear in the WHERE clause of the statement or in the HAVING clause of an aggregate query.
SELECT *
FROM details d
WHERE NOT EXISTS (
SELECT *
FROM items i
WHERE i.DetailID == d.ID
AND i.Item = 'A')
building on systempuntoout's solution:
SELECT details.*
FROM details
LEFT OUTER JOIN items ON details.ID=items.DetailID AND items.Item = 'A'
WHERE items.DetailID IS NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
