From: Martijn Tonies
Date: April 11 2006 10:41am
Subject: Re: A complex JOIN scenario
List-Archive: http://lists.mysql.com/mysql/196763
Message-Id: <029401c65d54$6f040bd0$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit




> > > 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?
>
> invoice_archive:
> +------------+----------+--------------------+
> | invoice_id | order_id | invoice_journal_id |
> +------------+----------+--------------------+
> |       1062 |     1035 |                  6 |
> |       1063 |     1036 |                  6 |
> |       1064 |     1037 |                  8 |
> +------------+----------+--------------------+
>
> invoice:
>
+------------+----------+-------------+-------------------+---------------+
> | invoice_id | order_id | customer_id | invoice_timestamp | invoice_total
|
>
+------------+----------+-------------+-------------------+---------------+
> |       1065 |     1038 |          63 | 1144744655        | 777
|
>
+------------+----------+-------------+-------------------+---------------+
>
> I hope this helps, sorry for being short on information but I haven't
> really woken up properly yet. More coffee..

If this needs to give you this result:

>
+------------+----------+--------------------+-------------------+----------
-----+
> | invoice_id | order_id | invoice_journal_id |invoice_timestamp |
invoice_total |
>
+------------+----------+--------------------+-------------------+----------
-----+
> |       1062 |     1035 |                  6 | NULL | NULL | NULL |
> |       1063 |     1036 |                  6 |..more NULL ...
> |       1064 |     1037 |                  8 |
> |       1065 |     1038 |          NULL| 1144744655        | 777
|

then a UNION is the only thing you can do. It's not a JOIN at all. Oh wait,
you can call it a FULL JOIN, which simply returns results from both tables
in the join, but hey, that's actually the "lack of a join", IMO :-)

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