Raghu Raghu - 1 month ago 13
SQL Question

SQL Joins 3 tables ---data not pulled from inner table

I have this SQL Query that pulls data from 3 tables. I am unable to get any columns from table3. What might be the issue?

SELECT table1.col1 COUNTRY,
table2_alias1.col3 STATE,
table2.GEOG_LOCATION,
table2.GEOG_LOCATION_pRE,
table2.T2col4,
table2.T2col5,
TABLE3_ALIAS1.ZIP,
TABLE3_ALIAS1.T3col2,
TABLE3_ALIAS1.T3col3,
TABLE3_ALIAS1.T3col4
FROM table1,
table2 table2_alias1,
table2,
(SELECT *
FROM table3
WHERE ( col5 = 'XXX'
OR col5 = 'YYY' )
AND col6 = 'ZZZ')TABLE3_ALIAS1
WHERE table1.col3 = 'AAA'
AND table1.col1 = table2_alias1.STATE
AND table2_alias1.STATE = table2.col7(+)
AND table2.STATE = TABLE3_ALIAS1.col6(+)
ORDER BY
2,
4,
10,
9


SAMPLE OUTPUT(HIGHLIGHTING THE MOST IMP DATA AS MEANINGFUL COL NAMES)

COUNTRY|STATE| GEOG_LOCATION | T2COL2 | T2col3 | t2col4 | zip | T3col2 |T3col4

USA CA LosAngls LosAngls-1 0 y 90001
USA CA SanFrans SanFrans-4 0 y 94101
USA CA Freemont Freemont-A 0 n 94538


Only the first 5 columns have data and the columns INCLUDING zip(that come from table3) and beyond do not contain any data when the query is run

RDBMS

TABLE1 TABLE2 TABLE3

T1_COL1 GEOG_LOCATION T3_COL1
GEOG_LOCATION T2_COL2 T3_COL2
T1_COL3 GEOG_LOCATION_pRE GEOG_LOCATION
T1_COL4 T2_COL4 T3_COL4
T1_COL5 T2_COL5 T3_COL5
T1_COL6 T2_COL6 T3_COL6
T1_COL7 T2_COL7 T3_COL7
T1_COL8 T2_COL8 T3_COL8
T1_COL9 T2_COL9 T3_COL9
T1_COL10 T3_COL10


The three tables are linked by the column startihg with HHH. They share then same data. The column starting with FFF(3rd one in table 2) has a recursive relation with the 1st column of table2(HHH_col1).

Answer

Try running the nested query on it's own:

     SELECT *
     FROM   table3
     WHERE  ( col5 = 'XXX'
               OR col5 = 'YYY' )
            AND col6 = 'ZZZ'

Do you get data back?

Also, might be worth prefixing the column names in the query above with the table name, just to be safe.

Then, try joining it to table2:

     SELECT *
     FROM   table3, table2
     WHERE  ( col5 = 'XXX'
               OR col5 = 'YYY' )
            AND col6 = 'ZZZ'
            AND table2.col3 = table3.col6(+)

... and so on, rebuilding your query one step at a time, until the data is missing. Then you know which step is the culprit...