IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need one-one query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 34

PostPosted: Wed Mar 20, 2013 4:35 pm
Reply with quote

I have 2 Db2 tables. I need Query for One- one values. Data is given below

Input table 1:
-------------
Code:
Order               Order                   Prodcut
No                    Type                    No
------------         ------------           -------------------
2001                      1                        201
2002                      1                        201
2003                      1                        202
2004                      1                        203
2005                      1                        204
2006                      1                        205
2007                      1                        205


Input table 2:
-------------
Code:
Order No       Order Type        Product Name
----------        ------------         -------------------
2001                      1                       ART
2002                      1                       GRT
2003                      1                       MRT
2004                      1                       ABT
2005                      1                       CMT
2006                      1                       NPT
2007                      1                       DCP

Output should be like this
--------------------------

Code:
Order No       Order Type         Product No                Product Name
----------        ------------          -------------------        ----------------
2003                   1                        202                       MRT
2004                   1                        203                       ABT
2005                   1                        204                       CMT
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Mar 20, 2013 6:21 pm
Reply with quote

I've edited your code to add the BBCode Tags, hopefully the alignment is what you wanted to show. Done that, please learn to use BBcode tags, next time you post something which need that.

About your question, possibly a JOIN and simple WHERE will help here - but why 2001, 2006 and 2007 were dropped from 'output' - because the third column was repeating in value for them in table 1?
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Wed Mar 20, 2013 6:21 pm
Reply with quote

Not sure if I understood correctly.. Is it the exact Output you want it's just an xample...

I would could like

Select
A.Order No,
A.Order Type,
A.Product No,
B.Product Name

From Table 1 A, Table 2 B

Where A.Order No = B.Order No And
A.Oder Type = B.order Type


It will give you Output Like


2001 1 201 ART
2002 1 201 GRT
2003 1 202 MRT
2004 1 203 ABT
2005 1 204 CMT
2006 1 205 NPT
2007 1 205 DCP
Back to top
View user's profile Send private message
Keanehelp

New User


Joined: 27 May 2008
Posts: 71
Location: USA, CA.

PostPosted: Wed Mar 20, 2013 6:23 pm
Reply with quote

Sorry, I did not notice the date of the Post.... icon_redface.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Mar 20, 2013 6:26 pm
Reply with quote

Keanehelp wrote:
Sorry, I did not notice the date of the Post.... icon_redface.gif
What's wrong in that? Though the timestamp for our posts match but thread is current!
Back to top
View user's profile Send private message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 34

PostPosted: Wed Mar 20, 2013 7:13 pm
Reply with quote

My Output have only these values

2003 1 202 MRT
2004 1 203 ABT
2005 1 204 CMT
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Mar 20, 2013 7:20 pm
Reply with quote

so You gave a visual rule ...
translate that to an algorithmic one icon_evil.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Mar 20, 2013 7:27 pm
Reply with quote

Possibly this what OP is looking for
Anuj Dhawan wrote:
About your question, possibly a JOIN and simple WHERE will help here - but why 2001, 2006 and 2007 were dropped from 'output' - because the third column was repeating in value for them in table 1?
but OP has not confirmed it yet.
Back to top
View user's profile Send private message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 34

PostPosted: Thu Mar 21, 2013 11:56 am
Reply with quote

if product Number is repeated then no need to consider 2001, 2006 and 2007 records.

Could you please give same query for this.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Mar 21, 2013 2:29 pm
Reply with quote

If 'Repeating' means occurs more than 1 time
Code:
From
(Select Product_no from Tab1 group by Product_no having count(*) = 1) X
join Tab1 A on A.product_no = X.product_no
join Tab2 B on A.Order_No   = B.Order_No and A.Order_Type = B.order_Type

or
Code:
From Tab1 A
join Tab2 B on A.Order_No   = B.Order_No and A.Order_Type = B.order_Type
where not exists
(Select Product_no from Tab1 X where X.product_no = A.product_no and
   not (X.Order_No   = A.Order_No and X.Order_Type = A.order_Type )
   )
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top