List:General Discussion« Previous MessageNext Message »
From:Kim Christensen Date:April 11 2006 10:22am
Subject:Re: A complex JOIN scenario
View as plain text  
On 4/11/06, Martijn Tonies <m.tonies@stripped> wrote:
> 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 mean that my two tables (invoice and invoice_archive) both share a
common column name, invoice_id, and in my result example below I have
to rows, one in each table.

Am I too unclear?

Note that my example is not a realy result set, it's only here to show
you what kind of query I'm trying to build!

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

--
Kim Christensen
kim.christensen@stripped
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