I do not try it :
select invoice_id, order_id, customer_id, invoice_timestamp,
invoice_total, null
from invoice
union all
select invoice_id, null, null, null, null, invoice_journal_id
from invoice_archive
Santino
At 12:08 +0200 11-04-2006, Kim Christensen wrote:
>Hello everyone
>
>I have a pretty complex (at least for me) scenario which I really
>can't work out the logic of. I have two tables with the following
>layout:
>
>table 1: invoice
>----------------------------------
>invoice_id
>order_id
>customer_id
>invoice_timestamp
>invoice_total
>...etc (only unique names)
>
>table 2: invoice_archive
>----------------------------------
>invoice_id
>invoice_journal_id
>
>
>By doing a UNION select on the invoice_id's of these two tables, I get
>a list of ID's from both of them. Since they are unique, this works
>pretty good - but I want all the columns aswell! I've seen some JOIN
>examples before, which fetches values from two tables (B and C) based
>on values from table A, but since the ID is only present in one of
>them at a time in my scenario, I really can't work it out.
>
>I want to have my output displayed something like this:
>
>+------------+----------+-------------+-------------------+---------------+--------------------+
>| invoice_id | order_id | customer_id | invoice_timestamp |
>invoice_total | invoice_journal_id |
>+------------+----------+-------------+-------------------+---------------+--------------------+
>| 1 | 1000 | 1 | 123131332131 |
>1232 | NULL |
>| 2 | NULL | NULL | NULL |
>NULL | 21 |
>+------------+----------+-------------+-------------------+---------------+--------------------+
>
>As you probably understand from this example above, each "invoice_id"
>is only present in one of the tables, therefore the NULL values.
>
>How can I accomplish something like this?
>
>--
>Kim Christensen
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
