On 4/11/06, Martijn Tonies <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
