Solved

SQL Full Outer Join Syntax for Multiple tables :-)

Posted on 2010-09-13
16
787 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

679 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