Solved

SQL Full Outer Join Syntax for Multiple tables :-)

Posted on 2010-09-13
16
782 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now