Connecting 3 tables, and getting the output

Mahendran3784 · New User Joined: 04 May 2007 Posts: 20 Location: INDIA

I have three tables A, B, C. All these 3 tables have a common key column, say KEY.

Table - A, Columns - KEY, COL1, COL2
Table - B, Columns - KEY, COL1, COL2
Table - C, Columns - KEY, COL1, COL2

In the output result set, I wish to have few columns from A and few columns from C which has A.KEY = C.KEY AND a satisfying condition in table B.

I tried the below query, and it seems it is going to take days. Can someone help me fine-tune/re-write this query?

SELECT A.COL1, A.COL2, C.COL1
FROM A INNER JOIN C
ON
A.KEY = C.KEY
WHERE
A.KEY IN (SELECT KEY FROM B WHERE COL1 IN ('VAL1','VAL2')

dick scherrer · Posted: Thu Nov 11, 2010 1:22 am

Hello,

Suggest you consider eliminating unwanted rows from table B and then join only the rows you want with tables A/C. . .

Mahendran3784 · New User Joined: 04 May 2007 Posts: 20 Location: INDIA

d.sch.,
Yeah, we dont want any rows from table B in the results set, just that table will be used for an extract criteria. I am trying the below query now. Will update you if I am successful?
SELECT A.COL1, A.COL2, C.COL1 FROM A INNER JOIN B ON A.KEY=B.KEY INNER JOIN C ON A.KEY=C.KEY WHERE B.COL1 IN ("VAL1", "VAL2")
I see this is also taking more time.
Thanks!

dick scherrer · Posted: Thu Nov 11, 2010 2:19 am

Hello,

Just because you use table criteria to eliminate most of the rows does not imply that any of the results have to be from table B.

If you don't want to use this kind of sql, you can always extract the useful rows from table to a sequential file and use this file to drive the sql that actually selects the rows you do want.