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

Yes, that I understand. But WHAT is your current data in both tables
from which you can/should derive your wanted resultset?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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