List:General Discussion« Previous MessageNext Message »
From:Santino Date:April 11 2006 10:46am
Subject:Re: A complex JOIN scenario
View as plain text  
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

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