From: Philip Tsoiasue To: All

I find one difference between SQL and Query to be a particular problem in SQL. Query has the ability to return records that do not necessarily have a matched record in the secondary file. For example, I have a master file that allows users to specify a code in one of the fields. If the code is specified, I would like to print the description of the code (stored in a secondary file). Query returns all records in the primary file, but SQL returns only the records with codes because it cannot find the blank code in the secondary file. Any alternatives?

From: Doug Payton To: Philip Tsoiasue

Here's the only alternative I know of:

 SELECT mcode, 'Not Found' FROM master WHERE mcode not in (SELECT scode FROM secfile) 

This prints all codes in the master file that are not in the secondary file. Returning to your problem, I don't see a way to do this in one SQL statement. Maybe you could merge two SELECT statements with the UNION clause.

From: Sharon Cannon To: Philip Tsoiasue

After reading Doug's message, I think I see an easy way to accomplish this by using the following SQL statement:

 SELECT mcode, desc FROM master, secfile WHERE mcode = scode UNION ALL SELECT mcode, 'Not Found' FROM master WHERE mcode not in (SELECT scode FROM secfile WHERE scode = master.mcode)