Finding records that don't share a relationship

Question

Possible Duplicate:
SQL - find records from one table which don’t exist in another

I have the following (simplified) schema in MySQL:

simplified schema

An arrow indicates a one (non-arrow side) to many (arrow side) relationship.

I want to determine, for which delivery_zone_weeks, does a customer not have a weekly_order.

Please also include few sample result set rows from the query you're hoping to create. — O. Jones, Jan 01 '13 at 00:39
Also see http://stackoverflow.com/questions/544094/mysql-finding-rows-that-dont-take-part-in-a-relationship?rq=1. Both these are listed in the **Related** sidebar, how'd you miss them in your search? — Barmar, Jan 01 '13 at 00:47

score 0 · Answer 1 · answered Jan 01 '13 at 01:33

It is difficult to understand fully without structures, sample data and expected result, but seems to be risking it a bit you need

SELECT * FROM DELIVERY_ZONE_WEEK WHERE ID_DELIVERY_ZONE_WEEK NOT IN
(SELECT WO.ID_DELIVERY_ZONE_WEEK FROM CUSTOMER C
JOIN SHIPPING_ADDRESS SA
    ON C.ID_CUSTOMER = SA.ID_CUSTOMER
JOIN WEEKLY_ORDER WO
    ON SA.ID_SHIPPING = WO.ID_SHIPPING
WHERE C.ID_CUSTOMER = @ID_CUSTOMER)

Finding records that don't share a relationship

1 Answers1