Query only records that have subrecords

mkobey
mkobey used Ask the Experts™
on
I am creating a query that runs a report of the master table.  If there is a record in the subtable, how would I write an SQL statement that will return "YES" if there are related records in the subtable and "NO" if there are no related subrecords?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi mkobey,

Outer join the master table to the subtable.  

SELECT mastertable.key, case when subtable.key is NULL then 'NO' else 'YES' end
FROM mastertable
LEFT OUTER JOIN subtable
  ON mastertable.subkey = subtable.key


Good Luck,
Kent


Author

Commented:
My mistake, I meant to post in microsoft access 2007.

Thanks,
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Please credit, Kent, with the methodology as I am simply offering translation to MS Access syntax for you.

SELECT mastertable.key, case when subtable.key is NULL then 'NO' else 'YES' end
FROM mastertable
LEFT OUTER JOIN subtable
  ON mastertable.subkey = subtable.key

Becomes this in MS Access:

SELECT mastertable.key, IIF(ISNULL(subtable.key), 'NO', 'YES')
FROM mastertable
LEFT OUTER JOIN subtable
  ON (mastertable.subkey = subtable.key)

Regards,
isa
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Believe single quote works, but double quotes for MS Access.

SELECT mastertable.key, IIF(ISNULL(subtable.key), "NO", "YES")
FROM mastertable
LEFT OUTER JOIN subtable
  ON (mastertable.subkey = subtable.key)

Author

Commented:
thanks all for your help.
It seems to me that mwvisa1 should get most of the credit here.  My answer works for SQL (and most other database engines).  His answer works for Access, which is what the poster wanted.


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial