List:General Discussion« Previous MessageNext Message »
From:Kim Christensen Date:April 11 2006 10:08am
Subject:A complex JOIN scenario
View as plain text  
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
Thread
A complex JOIN scenarioKim Christensen11 Apr
  • Re: A complex JOIN scenarioMartijn Tonies11 Apr
    • Re: A complex JOIN scenarioKim Christensen11 Apr
  • Re: A complex JOIN scenarioMartijn Tonies11 Apr
    • Re: A complex JOIN scenarioKim Christensen11 Apr
  • Index merge optimization (with OR) and table joinsStuart Brooks11 Apr
    • Re: Index merge optimization (with OR) and table joinssheeri kritzer4 May
      • Re: Index merge optimization (with OR) and table joinssheeri kritzer4 May
  • Re: A complex JOIN scenarioMartijn Tonies11 Apr
    • Re: A complex JOIN scenarioKim Christensen11 Apr
  • Re: A complex JOIN scenarioSantino11 Apr
  • Re: Index merge optimization (with OR) and table joinsStuart Brooks23 May
Re: A complex JOIN scenarioKim Christensen11 Apr
  • Innodb import tuning on Sun T2000Russell Horn7 Jul