Home > IT Answers > Database > Simple but not so
Help

Question

  Asked: Oct 5 2006   9:18 AM GMT
  Asked by: ilink88

Simple but not so


Database, Oracle, DB2, SQL, Development, Web development tools

I have 2 table
1) Product_Inf and
2) Product_attribute_details

I have prod_key, dye_class, prod_range in both the table.

Their is no relation exist between both the table than how can i find
the records available in Product_Inf but not in Product_attribute_details?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Oct 5 2006  9:57 AM GMT  by BeerMaker




something like this:

select * from table1
where Productkey not in (select productKey from Table2)
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Database, Oracle and Development.

Looking for relevant Database Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

SheldonLinker  |   Oct 5 2006  11:00AM GMT

The method last shown will work, but is not the fastest. The following is:

DB2:

SELECT *
FROM firstTable a
EXCEPTION JOIN secondTable b ON
a.field=b.field

Oracle:

SELECT *
FROM firstTable AS a,
secondTable AS b
WHERE a.field=b.field(+) AND
b.field IS NULL

SQL Server:

SELECT *
FROM firstTable AS a
LEFT OUTER JOIN secondTable AS b ON
a.field=b.field
WHERE b.field IS NULL

 

jigarrshah  |   Oct 6 2006  7:31AM GMT

select PI.*
from Product_Inf PI
left join Product_attribute_details PAD
on (PI.prod_key = PAD.prod_key)
where PI.prod_key is not null

or

select PI.*
from Product_Inf PI
where
not exists
(
select
‘x’
from
Product_attribute_details PAD
where PI.prod_key = PAD.prod_key and
PI.dye_class = PAD.dye_class and
PI.prod_range = PAD.prod_range
)

 

vikramnag  |   Oct 6 2006  7:41AM GMT

I would not suggest a join here. Go with either the Exists or the IN clause.

Vikram Nag

 

jigarrshah  |   Oct 6 2006  7:42AM GMT

sorry, it should be,

select PI.*
from Product_Inf PI
left join Product_attribute_details PAD
on (PI.prod_key = PAD.prod_key)
where PAD.prod_key is null

 

BeerMaker  |   Oct 6 2006  8:50AM GMT

Hey, ilink88, since we’ve helped you out with your query. why don’t you satisfy our curiosity here and compare the performance of the Join and “Not IN” methods.

Use the show execution plan on Query Analyzer and post the results. Check your indexes, etc to see what you can do to make it fly.

Cheers