List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 11 2006 10:12am
Subject:Re: A complex JOIN scenario
View as plain text  
Kim,

> 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.

What exactly do you mean by "present in one of them"? Can you give
us the sample data from which you derive the result below?

> 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?

See above.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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