Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Outer join query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
hariibm

New User


Joined: 05 Feb 2007
Posts: 61
Location: Chennai

PostPosted: Mon Mar 19, 2012 3:58 pm    Post subject: DB2 Outer join query
Reply with quote

Hi,

I need a SQL query for the below requirement.

I have two tables to join lets say Table1, Table2.

Table1 rows must be there in the result so I am using the LEFT OUTER JOIN between these two.

My requirement is:

Code:
Table1            Table2
----------         ---------
field1   field2         field1   field2   field3
code1   A         code1   *   ***             
code2   B         code1   P   ***
            code1   P   C


Outer join is based on the field1 of Table1 and field1 of Table2.

the result after Outer join is like below:

Code:
code1   A   code1   *   ***
code1   A   code1   P   ***
code1   A   code1   P   C 
code2   B   null   null   null


this is for finding the nearest match. so in the above result set for the (code1, A), I need to have only (code1 P C) in the result set.
ie, like below.

Code:
code1   A   code1   P   C 
code2   B   null   null   null


ordering the result by field1, field2(of table1), then by field1 in ascending order and then field2, field3 by descending order
will bring the required result at the top for each group(like below). But I am not sure how to eliminate the below two rows.

So, I need the 1st and 4th row from the below result set. Please help me with the query.
Code:
code1   A   code1   P   C 
code1   A   code1   P   ***   (these two rows
code1   A   code1   *   ***   (should be eliminated from the result set.)
code2   B   null   null   null

Thanks,
Hari
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Mar 19, 2012 5:03 pm    Post subject:
Reply with quote

Hi,

Please start using code tags for the input data

Do you want to select only row from table B or all rows for code1 which will satisfy your criteria?

Also is there any specific criteria that you dont want to slect rows from table B?

From Inputs you have provided it looks like you dont want to select rows wheer its returning Table 2 fields having value as '*'

Regards,
Chandan
Back to top
View user's profile Send private message
hariibm

New User


Joined: 05 Feb 2007
Posts: 61
Location: Chennai

PostPosted: Mon Mar 19, 2012 7:25 pm    Post subject:
Reply with quote

Hi Chandan,

Please start using code tags for the input data
Thanks for putting the things right. I was not aware.

Do you want to select only row from table B or all rows for code1 which will satisfy your criteria?

Also is there any specific criteria that you dont want to slect rows from table B?

From Inputs you have provided it looks like you dont want to select rows wheer its returning Table 2 fields having value as '*'

I need to select all rows from Table1 with the nearest match in Table2 for every rows of Table1.
As per the example row from Table2 removed from result set because there is a row with exact match found in Table2.(with exact match for P and C).
yes I forgot to mention, apart from the join criteria, the P, C(or different values) will be input to this query. So the row with exact match from Table2 should be taken. If not then the row with partial match(like P, ***) should be taken and if none of the matches found, then the ultimate matching row is the row with *, ***).

I hope it can be understood. Let me know if there is any confusion.

Regards,
Hari
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Mon Mar 19, 2012 10:44 pm    Post subject:
Reply with quote

Hariibm,

Can you please define the term exact matching ?

You second explanation is like "ignotum per ignotius"

If I understand your requirement correctly

Is it something like you always try to field 2 value when it is "P" and the key of the record in Table2 matching with key of the record in TABLE1

and then if present you wanted to select the record from table2 having field 3 value of c

If the above record is not present

You wanted a record from table2 having field2 as P and field3 as any value ( Ofcourse it cant be C)


And even if the both the above are not present

You wanted a record from table2 having field2 as any value (ofcourse cant be P) and field3 as any value ( it cant be C and other values)

and apart from this the unmatched records from Table1

Please confirm
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Mar 20, 2012 4:04 pm    Post subject:
Reply with quote

Code:
select * from
table1 A
left join table
(select * from table2 B where b.field1 = a.field1
 order by b.field2 desc, b.field3 desc
 fetch first 1 row only) B2 on 1=1
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us