Getting all rows from both tables

[hessodreamy]

Hi. How do I get all rows from 2 tables, where there may not be a corresponding rows in EITHER table?
I have 2 tables 'online orders' and 'offline orders', both with 'date' and 'order total' columns. I want to combine them into 1 table, but there may be dates in 'online orders' which are not in 'offline orders' and vice versa.
Cheers

 

[TonyGroves]

You could use something like:
[tt]
SELECT date,ordertotal FROM onlineorders WHERE ...
UNION ALL
SELECT date,ordertotal FROM offlineorders WHERE ...
[/tt]

 

[hessodreamy]

Yeah, but then I get loads of duplicate date entries. Ideally what I want is a table like this:

date onlineorders offlineorders
25/06/05 26 30
26/06/05 null 26
27/06/05 13 null
etc...

 

[azzazzello]

Do what Tony said and ORDER BY date?

 

[r937]

select daDate
     , sum(case when daType='online '
                then 1 else 0 end) 
          as onlineorders
     , sum(case when daType='offline'
                then 1 else 0 end) 
          as offlineorders
  from (                
        select datecolumn as daDate
             , 'online '   as daType
          from onlineorders
        union all
        select datecolumn as daDate
             , 'offline'   as daType
          from offlineorders
       ) as u
group
    by daDate

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[azzazzello]

ack, I meant GROUP BY

 

[hessodreamy]

To make things extra tough for you, I should have mentioned that I'm stuck with version 4.0.23, so can't use subqueries.
Just to make it a little more fun!

 

[r937]

yes, you should've

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)