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


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

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