| Joining on Nonmatched Key [message #19313] |
Mon, 11 March 2002 14:19  |
uma
Messages: 67
Registered: May 2001
|
Member |
|
|
I have 2 tables
1.PRODUCT 2.PRODUCT TYPE
Product table is like this
Product-id
product-code
product-type-desc
Product_type table is like this
Product_type_id
Product_type_desc
Product_type_code
I want to join product and Product type with Product_type_desc. Some times the Product_type_desc in PRODUCT table is not in Product_type TABLE. In that case I don't want to drop the PRODUCT table data but instead I have a value to match with product_type_desc as 'NOT CLASSIFICIED for the product_id equal 1. How to do this? Can I use DECODE?.
SELECT A.PRODUCT_ID,B.PRODUCT_TYPE_ID,B.PRODUCT_TYPE_DESC,B.PRODUCT_TYPE_DESC
FROM PRODUCT A
,PRODUCT_TYPE B
WHERE
a.PRODUCT_ID=1
and
decode(a.product_type_desc,b.product_type_desc,a.product_type_desc,'NOT CLASSIFIED')=B.PRODUCT_TYPE_DESC
But this DECODE is not giving correct results. Any Idea or any other suggestions.
Thanks in advance,
Uma
|
|
|
|
|
|
| Re: Joining on Nonmatched Key [message #19318 is a reply to message #19313] |
Mon, 11 March 2002 21:13  |
Srihari
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
Your Problem is not clear .Anyway try this query.
select a.*,b.* from product a,product_type b where a.product_type_desc = b.product_type__desc
union
select a.*,b.* from product a,product_type b where a.product_id='1' and a.product_type__desc<>b.product_type__desc and b.product_type_desc='unspecified'
IF this doesn't work try Outer Join
Revert Back with more details.
Srihari
|
|
|
|
| Re: Joining on Nonmatched Key [message #19334 is a reply to message #19318] |
Tue, 12 March 2002 05:34 |
uma
Messages: 67
Registered: May 2001
|
Member |
|
|
This query looks good. But the second query is always true even I have a matching key because of not equal selection and b.product_type='not classified'. So I am getting one extra row for the matching key.
For example if I have product_desc 'DIES' in both the tables first query returns the matching value with actual product_type_id and the second query returns product_type_desc='not classified' and its product_type_id for the product_desc 'DIES'. I hope you are clear. If not please let me know the specific place you are not clear.
I tried to use outjoin but doesn't work.
Thanks for your time.
Uma
|
|
|
|
| Re: Joining on Nonmatched Key [message #19343 is a reply to message #19313] |
Tue, 12 March 2002 11:24 |
uma
Messages: 67
Registered: May 2001
|
Member |
|
|
How to do the outer join here? You mean like this
select a.product_id,b.product_type_id
from a.product
b.product_type
where
a.product_id=1
and
a.product_type_desc=b.product_type_desc(+)
But this gives me no result. Is there any thing wrong in my query or what I need to do? Help is appreciated.
Uma
|
|
|
|
| Re: Joining on Nonmatched Key --Updated [message #19344 is a reply to message #19313] |
Tue, 12 March 2002 12:28 |
uma
Messages: 67
Registered: May 2001
|
Member |
|
|
I will give some examples.
My PRODUCT table is like this
product_id product_type_desc product_code
1 DIES AB
1 DIES TEST AC
2 NOT DEFN AD
AND my PRODUCT_TYPE table is like this
Product_type_id product_type_desc product_type_code
40 DIES DD
41 NOT CLASSIFIED NN
43 NOT FOUND MM
I want my out put like this
product_id product_type_desc product_code prod_type_id
1 DIES AB 40
1 DIES TEST AC 43
2 NOT DEFN AD 41
I think it is more detailed. My driver is the product table. I don't want to drop any records from this table. Initially I thought I can manage the product_id not equal 1 with an UNION. My only problem is, if product_id is 1 and no matching desc then I dont want to drop them. By using the above 4 resultant set I have to populate other table. Thanks a lot for your help. I am kind of stuck with this problem. Any questions please ask ASAP.
Thanks,
Uma
P.S: I am not sure the tables I provided may not be formated after I posted. Sorry.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
