one-to-many relationship - pulling only records that don''t have a match

• Dave Crandell

  SSC Rookie

  Points: 29

  More actions

  April 26, 2005 at 3:06 pm

  #66052

  Hello -

  I have two tables: titles and publishers...all records in titles are tied to one publisher but not all publishers have corresponding records in the titles table. I need to know how to pull only publishers who do not have a corresponding title. I want to create a report of only these publishers.

  I wrote an SQL statement like the following:

  SELECT txtPublisher FROM tblPubslishers LEFT OUTER JOIN tblTitles ON tblPublishers.autPubId = tblTitles.intPubId

  This returns all records in publishers instead of just those I need.

  I have also made a WHERE NOT LIKE and a WHERE tblPublishers.autPubId <> tblTitles.intPubId statement to try but none have worked...

  It seems like this should be really simple but I am apparently missing something.

  Any help is appreciated.

  Thanks,

  Dave

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  April 26, 2005 at 3:07 pm

  #555054

  Do the outer join and filter where table2.key IS NULL

• Dave Crandell

  SSC Rookie

  Points: 29

  More actions

  April 26, 2005 at 3:10 pm

  #555056

  Whoa Buddy!

  That's IT!

  Thank you!

  That pulls the records I WANT!