mkobey
asked on
Query only records that have subrecords
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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)
SELECT mastertable.key, IIF(ISNULL(subtable.key), "NO", "YES")
FROM mastertable
LEFT OUTER JOIN subtable
ON (mastertable.subkey = subtable.key)
ASKER
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
Kent
ASKER
Thanks,