Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Full Outer Join Syntax for Multiple tables :-)

Posted on 2010-09-13
16
Medium Priority
?
799 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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