You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

SQL Question on Full Outer Join

2 replies [Last post]
Guest's picture
Guest

Hi, I am writing a sql select statement. First, I need to get all courses and if they have textbooks, include the textbooks. (this is my new data extract). That's the first select and it works great. Then I need to take this file and do a full outer join with another file. (this is my previous old data extract, it does contain some extra fields, so not the exact same table layout). I should get a mixture of some records are only in the first select, others in both files and some only in the file I'm joining with. It should have the first select's data on a row and then on the same row the data from file c. The idea is to be able to tell by looking at the row, if the data is new (Table A only), or if the data is changed (Table A and Table C) or if the data has been deleted (Table C only). Ex:
Table A data ...... Table C data
Any help is appreciated! Thanks, Mary

select b.subject
, b.catalog_nbr
, b.acad_career
, b.descr
, b.strm
, b.session_code
, b.class_section
, a.ssr_txbdtl_seqno
, a.ssr_txbdtl_title
, a.ssr_txbdtl_isbn
from sysadm.ps_ssr_cls_txb_dtl a,
sysadm.ps_class_tbl b
where a.crse_id (+) = b.crse_id
and a.strm (+) = b.strm
and a.crse_offer_nbr (+) = b.crse_offer_nbr
and a.class_section (+) = b.class_section
and b.strm = '2102'
FULL OUTER JOIN ps_uni_textbk_skew c
ON (
subject = c.subject
AND catalog_nbr = c.catalog_nbr
AND acad_career = c.acad_career
AND strm = c.strm
AND session_code = c.session_code
AND class_section = c.class_section
AND ssr_txbdtl_seqno = c.ssr_txbdtl_seqno)
ORDER BY
subject
, catalog_nbr
, acad_career
, strm
, class_section ;

westendorf's picture
User offline. Last seen 8 years 48 weeks ago. Offline
Joined: 10/16/2010
Posts: 2
Re: SQL Question on Full Outer Join

This sql is going to be used with Oracle's sQL in case it makes a difference.
thanks again,
mary

bosmanjc's picture
User offline. Last seen 8 years 48 weeks ago. Offline
Joined: 07/23/2010
Posts: 14
Re: SQL Question on Full Outer Join

I'm thinking you should be able to identify by looking at columns that come back as null value. if they come back as null value after the outer join, they were not on the table. eg cols from tbl a have values, but tbl b are null, outer join found row on tbl a but not tbl b.