View previous topic :: View next topic
|
Author |
Message |
naveensrimf
New User
Joined: 04 Oct 2005 Posts: 34
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
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 |
|
|
Keanehelp
New User
Joined: 27 May 2008 Posts: 71 Location: USA, CA.
|
|
|
|
Sorry, I did not notice the date of the Post.... |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Keanehelp wrote: |
Sorry, I did not notice the date of the Post.... |
What's wrong in that? Though the timestamp for our posts match but thread is current! |
|
Back to top |
|
|
naveensrimf
New User
Joined: 04 Oct 2005 Posts: 34
|
|
|
|
My Output have only these values
2003 1 202 MRT
2004 1 203 ABT
2005 1 204 CMT |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
so You gave a visual rule ...
translate that to an algorithmic one |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
naveensrimf
New User
Joined: 04 Oct 2005 Posts: 34
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
|