Solved

SQL Full Outer Join Syntax for Multiple tables :-)

Posted on 2010-09-13
16
785 Views
Last Modified: 2012-05-10
I have three tables. Table A, Table B and Table C. These tables contain a date column and an Integer column. I want to join all three columns by the date into a third table called Results. This table will have one date column and a column for tableA data, tableB data and tableC data. I attached a spreadsheet with a sample of the data. I need to know how to write the query to do this. In the result query I would like nulls to show as zeros. The zeros are now in red just for display.

also the resulting data set should only have one row for each date.

thank you. I have been pulling my hair out all day with this one :-(
EE-Example.xls
0
Comment
Question by:imonfireDAMMIT
  • 8
  • 7
16 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33666872
Using your table specification, try this query.
Select myDate
, Case When [A] Is Null Then '0' Else [A] End As Data_A
, Case When [B] Is Null Then '0' Else [B] End As Data_B
, Case When [C] Is Null Then '0' Else [C] End As Data_C
From
(Select A.Date_A As myDate, A.Data_A As myData, 'A' As myColumn
    FROM Table_A A
	Union 
	Select B.Date_B As myDate, B.Data_B As myData, 'B' As myColumn
    FROM Table_B B
	Union
	Select C.Date_C As myDate, C.Data_C As myDate, 'C' As myColumn
    FROM Table_C C
	) As SourceTable
Pivot
(
Max(myData)
For myColumn IN ([A], [B], [C])
) As PivotTable
Order By myDate

Open in new window

0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 33666972
OK cool I will try this as soon as i get home :-) thank you I will let you know how this worked when im done.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33668233
without using PIVOT, you can even try this :

select date_result,
nvl(( select x.data_1 from tab_1 x where x.date_1 = m.date_result ),0) data_a  ,
nvl(( select x.data_2 from tab_2 x where x.date_2 = m.date_result ),0) data_b,
nvl(( select x.data_3 from tab_3 x where x.date_3 = m.date_result ),0) data_c
from
( select date_1 Date_Result from tab_1
  union
  select date_2 from tab_2
  union
  select date_3 from tab_3 ) m
order by date_result;
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33668265
we can even do it in the other way using FULL OUTER JOINS... try the below :

select nvl(nvl(date_1,date_2),date_3) mydate,
nvl(data_1,0) data_a,
nvl(data_2,0) data_b,
nvl(data_3,0) data_c
from tab_1 a full outer join tab_2 b
on ( a.date_1 = b.date_2 ) full outer join tab_3 c
on ( b.date_2 = c.date_3 )
order by mydate ;

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33668276
Just NOTE that whatever query you use, if there is a time portion store in your date column, then you need to use TRUNC(date_a), TRUNC(date_b), TRUNC(date_c) in all the occurrances of that query otherwise the query may or may not return any results.

I mean you need to use TRUNC() to all the date columns in your query.

Thanks,
0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 33668515
Nav Kum V,

What does the X represent. Is that an alias for the table?
select date_result, 
nvl(( select x.data_1 from tab_1 x where x.date_1 = m.date_result ),0) data_a  ,
nvl(( select x.data_2 from tab_2 x where x.date_2 = m.date_result ),0) data_b,
nvl(( select x.data_3 from tab_3 x where x.date_3 = m.date_result ),0) data_c
from 
( select date_1 Date_Result from tab_1 
  union
  select date_2 from tab_2
  union 
  select date_3 from tab_3 ) m
order by date_result;

Open in new window

0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 33668584
In my example I just was using sample data...my actual sql statement is shown below...both before and after I tried applying the fix that you suggested...
Before fix
SELECT   data_date, sql1.data_count, sql2.data_count, sql3.data_count
    FROM (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count,
                          base_discipline_file_types.filetype,
                          base_discipline_file_types.discipline
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB-EXIST'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql1
         FULL OUTER JOIN
         (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count,
                          base_discipline_file_types.filetype,
                          base_discipline_file_types.discipline
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql2
         ON sql1.data_date = sql2.data_date         
  FULL OUTER JOIN
  (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count,
                          base_discipline_file_types.filetype,
                          base_discipline_file_types.discipline
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'ELECT-EQUIP'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql3
         ON sql2.data_date = sql3.data_date
         
ORDER BY data_date;


--****************************************************
-- AFTER FIX applied (i dont have working yet)
--************************************************
I dont know if you can understand what i am trying to do. The tables I am building with SQL. I tried to use a simple example to avoid all of this extra code but its kinda hard to explain withou seeing....I am going to continue playing with your examples. :-)
SELECT   date_result,
         NVL ((SELECT sql1.data_count
                 FROM sql1
                WHERE sql1.date_1 = RESULT.date_result), 0) edwb_exist,
         NVL ((SELECT sql2.data_count
                 FROM sql2
                WHERE sql2.date_2 = RESULT.date_result), 0) edwb,
         NVL ((SELECT sql3.data_count
                 FROM sql3
                WHERE sql3.date_3 = RESULT.date_result), 0) elec_equip
        
    FROM (SELECT DISTINCT TRUNC(TO_DATE (base_graph_data.data_date),'DDD') AS date_result,
                          base_graph_data.data_count,
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB-EXIST'
                      AND base_graph_data.station_code = 'DUC' 
          UNION
          SELECT DISTINCT TRUNC (TO_DATE (base_graph_data.data_date),'DDD') AS date2, 
                          base_graph_data.data_count,
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB'
                      AND base_graph_data.station_code = 'DUC'
          UNION
          SELECT DISTINCT TRUNC (TO_DATE (base_graph_data.data_date),'DDD') AS date3,
                          base_graph_data.data_count,
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'ELECT-EQUIP' ) RESULT
ORDER BY date_result;

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33668755
you are right, X is an alias name.

So is that working for you or not ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33668765
Does you date column has time portion in it or no ? please confirm.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 33668793
ok.. i took the actual query which you gave before the fix in your last post and modified it to work for you using the FULL OUTER JOIN method which i gave with dumy tables.. so give it a try and let me know if this works for you.

SELECT  
nvl(nvl(sql1.data_date,sql2.data_date),sql3.data_date) data_date,
nvl(sql1.data_count,0) count_1,
nvl(sql2.data_count,0) count_2,
nvl(sql3.data_count,0) count_3
    FROM (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count,
                          base_discipline_file_types.filetype,
                          base_discipline_file_types.discipline
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB-EXIST'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql1
         FULL OUTER JOIN
         (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count,
                          base_discipline_file_types.filetype,
                          base_discipline_file_types.discipline
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql2
         ON ( trunc(sql1.data_date) = trunc(sql2.data_date) )
  FULL OUTER JOIN
  (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count,
                          base_discipline_file_types.filetype,
                          base_discipline_file_types.discipline
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'ELECT-EQUIP'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql3
         ON ( trunc(sql2.data_date) = trunc(sql3.data_date      )   )
ORDER BY data_date;

If this also does not work for you, then can you provide the output of sql1 alias in this query and provide output in excel .. similary for sql2 and sql3 as well.

Thanks
0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 33672105
OK i tried your fix and i get the same result as when i was doing the full outer join originally. THe error is ORA-03113 end of file on communication channel. It seems odd because my original syntax seemed correct... each individual statement runs fine

also i took out the Filetype and Discipline columns because they were just used for my testing. The only columns i am using are the data_date and the data_count

thanks for your help. This is driving me bananas. I do querys like this all the time but this full outer join has stumped me :-(
--******************************************************************************************
--Your Query with extra columns removed 
--******************************************************************************************
SELECT   
nvl(nvl(sql1.data_date,sql2.data_date),sql3.data_date) data_date,
nvl(sql1.data_count,0) count_1,
nvl(sql2.data_count,0) count_2,
nvl(sql3.data_count,0) count_3
    FROM (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB-EXIST'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql1
         FULL OUTER JOIN
         (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'EDWB'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql2
         ON ( trunc(sql1.data_date) = trunc(sql2.data_date) )
  FULL OUTER JOIN
  (SELECT DISTINCT TRUNC
                             (TO_DATE (base_graph_data.data_date),
                              'DDD'
                             ) AS data_date,
                          base_graph_data.data_count
                     FROM pidb.base_graph_data,
                          pidb.base_discipline_file_types
                    WHERE numerator = column_index(+)
                      AND base_graph_data.graph_type = 8
                      AND discipline = 'ELEC'
                      AND filetype = 'ELECT-EQUIP'
                      --and filetype = 'CADMD'  elec mech;
                      AND base_graph_data.station_code = 'DUC') sql3
         ON ( trunc(sql2.data_date) = trunc(sql3.data_date      )   )
ORDER BY data_date;

Open in new window

EE-Example.xls
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33673090
strange. are you running this in sqlplus or toad or where ?

can you try in sqlplus once please and let me know how it goes. i do not see any issues and it has to work.
0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 33673176
yeah that is what i am sayin!!! I tried in Toad and sql plus...
here is the sql plus error...


SELECT   NVL (NVL (sql1.data_date, sql2.data_date), sql3.data_date) data_date,
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


ORA-24323: value not allowed
Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-03114: not connected to ORACLE



I get the same ORA-03113 error in toad and i am positive that i am connected to oracle. That is such a vague error and online doesnt say too much.
0
 
LVL 1

Author Comment

by:imonfireDAMMIT
ID: 33673577
AND if i change it to an inner join it runs... maybe toad doesnt like me
0
 
LVL 1

Author Closing Comment

by:imonfireDAMMIT
ID: 33673984
well this worked and i guess so did my inital query. There is some weird oracle bug that i hit because i was using an ansi outer join on a view with nested subqueries...

thank you for your help :-)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33678181
So is the query working now or not yet ? need any further help ?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
backup and restore 21 29
Stored Proc - Rewrite 42 58
IIF in access query 19 24
SQLCMD Syntax 2 13
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question