Fetch data from mulitple table which has foreign key relationship

DBforumnewbie · #1 (**permalink**) 07-28-11, 06:08

Here is the Scenario :
I have 4 tables table1(COL1,COL2,cCOL3,COL4),table2(COL1,COL2,COL3 ,COL4,COL5),table3(COL1,COL2,COL3,COL4,COL5),table 4(COL1,COL2,COL3,COL4,COL5,COL6,COL7)
Table1
COL1,COL2 is the Primary key in Table 1
Table2
COL3,COL4 are the primary key in Table 2 and COL1,COL2 is the foreign key which references table 1
Table3
COL5 is the primary key of Table3 and COL1,COL2 are the foreign key references Table1
and COL3,COL4 are the foreign key references Table 2
Table4
COL6 is the Primary key in Table4 and COL1,COL2 are the foreign key references Table1
and COL3,COL4 are the foreign key references Table 2 and COL5 is the FK ref Table3

QUERY : i need to fetch the data from Table4 (COL7) where COL1,COL2 of table 4 must be equal to COL1,COL2 of table 1
COL3,COL4 of table 4 must be equal to COL3,COL4 of table 2
COL5 of table 4 must be equal to COL5 of table 3

SELECT T4.COL7
FROM Database.{SCHEMANAME}.{TABLE4}
AS T4 WHERE
(
SELECT T3.COL5
FROM Database.{SCHEMANAME}.{TABLE3}
AS T3 WHERE T3.COL5 = T4.COL5
)
AND
(
SELECT T2.COL3,T2.COL4
FROM Database.{SCHEMANAME}.{TABL2}
AS T2
WHERE T2.COL3 = T4.COL3
AND T2.COL4 = T4.COL4
)
AND
(
SELECT T1.COL1,T1.COL2
FROM Database.{SCHEMANAME}.{TABLE1}
AS T1
WHERE T1.COL1 = T4.COL1
AND T1.COL2 = T4.COL2
);

Could u guys please help me to rephrase the Query ?

DBforumnewbie · #2 (**permalink**) 07-28-11, 06:11

Please help me !!!

sathyaram_s · #3 (**permalink**) 07-28-11, 06:18

You have used sub-queries.

Do you know about Joins? That's what you should be using

Please refer to SQL manuals/tutorials.

DBforumnewbie · #4 (**permalink**) 07-28-11, 06:23

Thanks for the reply !!! Yeah i know
But im working in IBM MB where it will not support for joins

thatz why im going for subqueries

sathyaram_s · #5 (**permalink**) 07-28-11, 06:29

I don't know what IBM MB is . Message Broker ? BTW, you didn't specify about the MB restriction in your original post. Are there any other 'secrets' we need to know ;-)

If joins are not supported, you should use views as an alternative.

HTH

Sathyaram

dbzTHEdinosaur · #6 (**permalink**) 07-28-11, 06:41

i am a little confused. if all these tables are FK restricted, then if the FK exists in a chlild table, it must exist in the parent table.

so select from table 4????????

tonkuma · #7 (**permalink**) 07-28-11, 06:50

I agree with Dick.

If description of DBforumnewbie about foreign key were right,
all rows in Table5 should satisfy the requirements of the QUERY.
So, simple "SELECT col7 FROM table4" must be an answer for the QUERY.

Quote:

QUERY : i need to fetch the data from Table4 (COL7) where COL1,COL2 of table 4 must be equal to COL1,COL2 of table 1
COL3,COL4 of table 4 must be equal to COL3,COL4 of table 2
COL5 of table 4 must be equal to COL5 of table 3

Peter.Vanroose · #8 (**permalink**) 07-28-11, 07:01

What about

Code:

SELECT COL7
FROM Database.{SCHEMANAME}.{TABLE4} t4
WHERE EXISTS
(
 SELECT 1 FROM Database.{SCHEMANAME}.{TABLE3}
 WHERE COL5 = t4.COL5
)
AND EXISTS
(
 SELECT 1 FROM Database.{SCHEMANAME}.{TABL2}
 WHERE COL3 = t4.COL3 AND COL4 = t4.COL4
)
AND EXISTS
(
 SELECT 1 FROM Database.{SCHEMANAME}.{TABLE1}
 WHERE COL1 = t4.COL1 AND COL2 = t4.COL2
)

(Note the added "exists" entries.)
If all foreign keys are enforced, it would even suffice to verify that the columns 1 to 6 of TABLE4 are not NULL:

Code:

SELECT COL7 FROM Database.{SCHEMANAME}.{TABLE4}
WHERE COL1 IS NOT NULL
AND COL2 IS NOT NULL
AND COL3 IS NOT NULL
AND COL4 IS NOT NULL
AND COL5 IS NOT NULL
AND COL6 IS NOT NULL

DBforumnewbie · #9 (**permalink**) 07-28-11, 08:05

Thank u Peter !!!!
it actually helped me to solve my purpose !!!

Internet Services

Web Development

Digital Marketing

Consumer Tech