Left Outer Join using Fetch first 1 row

chandan.inst · Posted: Thu May 06, 2010 6:05 pm

Hi All,

I need help to solve one of my problem

I have three tables say A,B and C. I want to fetch data A and B tables, at the same time need to eliminate records depending on data in Table C.

Primary key for Table A is A1
Primary key for Table B is B1
Primary key of table C is combination of C1,C2
There can multiple rows in A which correspond to B1 in table B

My query is as below

chandan.inst · Posted: Thu May 06, 2010 6:38 pm

Hi Guys,

Sorry for this question I got the solution for this issue which is as below
Select A.A1
A.B1 --->(This is table A record which corresponds to B1 from Table B)
FROM A
WHERE
A.B1 in
(
Select B.B1
FROM B
left outer join C
on B.B1 = C.C1
AND C2 <> 'X'
)

Also please let me know if there is any alternate way

Regards,
Chandan

GuyC · Posted: Thu May 06, 2010 8:56 pm

I don't think your answer is correct :
your query will return all rows of A1 for which a row exists in B1 .
your left join with C doesn't do anything.

I don't understand the requirement exactly :
you want rows where A and B exist, but then ?
1) C does not exist => in output : Yes or no ?
2) C exists only 1 C2 = 'X' => in output : Yes or no ?
3) C exists only 1 C2 <> 'X' => in output : Yes or no ?
4) C exists 2 rows : one = 'X', other <> 'X' => in output : Yes or no ?

chandan.inst · Posted: Sat May 08, 2010 9:15 am

Hi GuyC..

Thanks for reply..

After going through your reply I also got to know my mistake and I am sorry I should have analyzed my query output before posting it..

I am quite confused with your questions..
My requirement is I want to eliminate records from results if there is a row present in C with C2 = 'X';there is chances that same C1 having more rows with C2<> 'X'

with my answered query it will give wrong output because my inner query left outer join will fetch C rows with C2<> 'X';but there is possibility of having same C1 having row with C2= 'X'

Now I could see tow options to get this done

Option 1:

Select A.A1
,B.B1
FROM A,
B
left outer join C
on B.B1 = C.C1
AND C2 = 'X'
WHERE A.A1=B.B1

After execution check the value of C2 and if it's 'X';skip the processing and go for next record

Option 2:

Select A.A1
A.B1 --->(This is table A record which corresponds to B1 from Table B)
FROM A ,B
WHERE
A.A1=B,B1
A.B1 not in
(
Select B.B1
FROM B
left outer join C
on B.B1 = C.C1
AND C2 = 'X'
)

As per my understanding Option 1 will have better performance

Please let me if I am missing something..

Regards,
Chandan

dbzTHEdinosauer · Posted: Sat May 08, 2010 1:46 pm

chandan.inst · Posted: Sat May 08, 2010 6:46 pm

Hi Dick,

Thanks for your reply..

Option 1 will give both rows with C2='X' as well as C2<> 'X' as because of left outer join only in later case C2 will be null..ya I need to have a cursor for this query and select C2 also in Select clause, Check value of C2 if it's X dont process the record..I hope I am not missing anything here..

Option 2 : I mentioned the wrong query again my apologies..it's a copy paste mistake..
I am giving the corrected queries again Sorry for the typos..

Option 1: it will be a cursor Check value of C2 , if it's X skip the processing

Select A.A1
,A.A2 --->(This is table A record which corresponds to B1 from Table B)
,C,C2
FROM A,
B
left outer join C
on B.B1 = C.C1
AND C2 = 'X'
WHERE A.A2=B.B1

Option 2: Not in clause will eliminate the unwanted records as C will have only one row with C2 = 'X' for given C1 (which corresponds to B1 in B)

Select A.A1
A.A2--->(This is table A record which corresponds to B1 from Table B)
FROM A ,B
WHERE
A.A2=B.B1
A.A2 not in
(
Select B.B1
FROM B , C
where B.B1 = C.C1
AND C2 = 'X'
)

Dick, I will be very thankful if you let me know how to perform EXPLAIN on these queries and differentiate the performance..

Kindly let me know if I missing anything

Regards,
Chandan

dbzTHEdinosauer · Posted: Sat May 08, 2010 6:58 pm

EXPLAIN

Included in this page are links to other subjects (e.g. plan table)
for which you may or may not need to familiarize yourself.

If you are not using vsn 8, then this link will guide you to your version.

sql guide for each vsn has a write-up and explanation on how to use EXPLAIN
and set-up your own PLAN table, which you have to do,
if you are going to generate EXPLAIN for your SQL thru SPUFI.

If, in your compile/link/bind jcl, the bind parm for EXPLAIN is yes, then you can look at the associated PLAN table populated by your compilation process.

the explain output can be viewed easily in SPUFI; either your own PLAN table,
or determine the proper qualifier (from your bind output) for the sub-system EXPLAIN generated by your compile process.

chandan.inst · Posted: Sat May 08, 2010 7:57 pm

Thanks for the info Dick..

Regards,
Chandan

GuyC · Posted: Mon May 10, 2010 1:27 pm