Union question...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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;
 
Back
Top