Query to return Parent rows that have no Child records

  • Thread starter Thread starter Andy79
  • Start date Start date
A

Andy79

I need to run a query on my database – I have a very simple setup..

Table: Conferences
Table: CR Commissions

Conferences [1-to-many] Commissions

I want a query to return all the conferences that have no associated
entries in the CR commissions table, is there an easy way to write the
last (***) part of my query in SQL?

Or do I need to some complicated approach that counts the number of
records in the CR Commissioning table for each conference and then
returns the list of conferences where this = 0..?

this is what i got so far, but this only shows records that do have at
least 1 record in the other table..

SELECT DISTINCTROW Conferences.Conference_Title,
Conferences.Conference_ID, Conferences.StartDate
FROM Conferences INNER JOIN [CR Commissioning] ON Conferences.
[Conference_ID] = [CR Commissioning].[Conference_ID]

WHERE (((Conferences.StartDate)>Now())

**** AND “that have no associated records in the
[CR Commissioning] table”) ;
 
Change the INNER JOIN to LEFT JOIN.

Put something like the following in the criteria:

[CR Commissioning].[Conference_ID] Is Null
 
Back
Top