Union question...

[Jesterhoz]

Hello All,

I know when you do a union query, it returns records where the selected
fields are unique. For instance the following SQL statement:

SELECT InvNo, CustCode, InvDate, InvAmt FROM FillTable UNION SELECT InvNo,
CustCode, InvDate, InvAmt FROM SalesTable;

will return records where any of the fields are unique. Some records may
have the same InvNo, but different CustCode, etc. My problem is this; After
this query runs, I want to then retrieve corresponding data associated with
the record from the original table. Example:

The query above returns two records, one from each table:

InvNo CustCode InvDate InvAmt
12345 568921 1/10/05 $65.50
12345 423594 8/20/04 $1234.60

Only one of the tables, FillTable, has a field called ExportDate. If the
record that is returned comes from that table I want the query results to
contain the above but also the ExportDate for that one record:

InvNo CustCode InvDate InvAmt ExportDate
12345 568921 1/10/05 $65.50 1/12/05
12345 423594 8/20/04 $1234.60

Does anyone have any ideas? Any help would be much appreciated.

Thanks,

Trevor

 

[Dennis]

Try either of these

SELECT InvNo, CustCode, InvDate, InvAmt, ExportDate FROM FillTable UNION
SELECT InvNo, CustCode, InvDate, InvAmt, NULL AS ExportDate FROM SalesTable;

if that doesn't work then try

SELECT InvNo, CustCode, InvDate, InvAmt, ExportDate FROM FillTable UNION
SELECT InvNo, CustCode, InvDate, InvAmt, Date() AS ExportDate FROM SalesTable;

 

[Jesterhoz]

Wonderful! The first option worked great. Thanks.

Trevor