Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible? (Oracle 10g)
Is it possible? [message #325471] Fri, 06 June 2008 05:22 Go to next message
spylad
Messages: 4
Registered: June 2008
Location: Somerset, UK
Junior Member
Hi All,

I have 3 tables in a database, one table that holds a list of products, one table that holds a list of locations and one table that holds a mixture of both of these toghether, so products at a location.

Each product should exist at a location, (there are 30,000 products at over 100 locations, so 3million product location records).

Some of these records failed to create, so there are some product locations missing.
Is there a query that I can write that will show me which records are missing?

Hope there's enough information there.

Cheers,
Spylad
Re: Is it possible? [message #325473 is a reply to message #325471] Fri, 06 June 2008 05:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It will be something like this

select ...
from (cartesian product of product, locations) a
     left outer join (history table)
    on (products, locations)
where history is null


I have only given you a idea. For more information about joins check this link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3175

Regards

Raj
Re: Is it possible? [message #325475 is a reply to message #325473] Fri, 06 June 2008 05:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could do something like
SELECT product_code, location_code
FROM    products,Locations
MINUS
SELECT product_code,location
FROM   product_at_location
Re: Is it possible? [message #325481 is a reply to message #325471] Fri, 06 June 2008 05:47 Go to previous message
spylad
Messages: 4
Registered: June 2008
Location: Somerset, UK
Junior Member
Thanks JRow and Ragaram.

Got my list now!

Thanks,
Andy
Previous Topic: Using an array as a select condition
Next Topic: cluster index
Goto Forum:
  


Current Time: Sun Dec 04 14:35:08 CST 2016

Total time taken to generate the page: 0.05482 seconds