Need help with query over three related tables I am designing a database for a publishing house to track magazine shipping and also to keep a history of it.

I have three tables (simplified):



Subscriber
  id (pk)
  subscribers_number (unique)
  first_name
  last_name
  shipping_address
  last_payment

Shipping
  id (pk)
  date
  issue

ShippingAddress
  id (pk)
  shipping_id (fk)
  subscriber_id (fk)
  subscribers_number
  first_name
  last_name
  shipping_address

NonDeliveryReport
  id (pk)
  shipping_address_id (fk)
  reason
  resolved


Subscriber is a list of people who pay to receive a monthly issue of their magazine. Not every subscriber is eligible to receive the next issue. To make it simple, a subscriber receives the next issue only if last_payment date is withing the last 60 days. There are some more conditions, but I'll get into that later.

When the next issue is ready to be shipped, a Shipping is created.

Then we select all eligible Subscribers and create a ShippingAddress for each one and copy the value of subscribers_number, first_name, last_name and shipping_address from the Subscriber to the new ShippingAddress. We do this so that we have an unmutable history of shipments, even if a Subscriber changes his/her address, or is deleted from the database. For ease of use, there is a nullable foreign key subscriber_id in addition to the hard copy of the subscribers_number.

If the mail company could not deliver to a subsriber's ShippingAddress, they return a non-delivery report to us. For each report, a NonDeliveryReport is created and references the affected ShippingAddress.

Now we are getting to the important part of my question:

When we create a Shipping, we have to select all Subscribers, that are eligible. Eligible is every subscriber, except for anyone, who has a non-delivery report attached to him, that was NOT resolved. (For simplicity's sake, we just ignore all other conditions.)

So I need two queries:


Select all subscribes that are eligible.
Select all subscribers that are not eligible.


Query #2 was managable and rather easy:

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  INNER JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
  INNER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.resolved IS NULL;


But the queries I wrote for case #1 always returned fewer or more results than expected. I am stuck on this since last week and just can't get it to work. I hope you could help me with that.

This isn't part of my main question, but any thoughs (in form of comments pls) about my database design are welcome. I was already thinking about adding a field deliverable (bool) to Subscriber and just fill in the value programmatically, when importing the NDRs, but am hesitant because that'd probably defeat the purpose of database normalization.

Edit #1: The closes I came to was the following query, but it returns too many rows. *sigh*

SELECT DISTINCT
  Subscriber.id
FROM Subscriber
  LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.Subscriber_id)
  LEFT JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.ShippingAddress_id)
WHERE NonDeliveryReport.korrigiert_am IS NOT NULL OR NonDeliveryReport.id IS NULL OR ShippingAddress.id IS NULL


Edit #2: After thinking even harder (I didn't think that was possible any more) I found a solution.

When non-eligible are subscribers that have an open NDR (resolved IS NULL), then eligible is anyone who as no NDR in addition to anyone who has more than 0 unresolved NDR.

-- List of ELIGIBLE Subscribers
SELECT DISTINCT Subscriber.id
FROM Subscriber
LEFT JOIN ShippingAddress ON (Subscriber.id = ShippingAddress.subscriber_id)
WHERE ShippingAddress.id IS NULL OR Subscriber.id NOT IN
  (
    SELECT ShippingAddress.subscriber_id
    FROM ShippingAddress
    INNER JOIN NonDeliveryReport ON ShippingAddress.id = NonDeliveryReport.shipping_address_id
    WHERE (NonDeliveryReport.id IS NOT NULL AND NonDeliveryReport.resolved IS NULL)
    GROUP BY ShippingAddress.subscriber_id
  )
ORDER BY Subscriber.id;
I believe you are wanting something a little like this;

SELECT DISTINCT
Subscriber.id
FROM Subscriber
 INNER JOIN ShippingAddress ON (Subscriber.id = 
ShippingAddress.subscriber_id)
  LEFT OUTER JOIN NonDeliveryReport ON (ShippingAddress.id = NonDeliveryReport.shipping_address_id)
WHERE NonDeliveryReport.shipping_address_id IS NULL OR NonDeliveryReport.resolved IS NOT NULL;