Help with a query 2

[CmPaiva]

Hello all.
I have the following problem:

Having a Product table as:
Id int
Status varchar(50)

and a Stock table as:
ProdId int (Product Id in move)
TrDate date (Stock move date)

Is it possible to have a list of all records from Product,
that do not exist in Stock since TrDate.
In other words, retrieve all products without movement at a specified date?

I'd tryed EXIST sub-querie, without sucess...
If anybody can help me, I'll be very grateful.

Thanks in Advance,
Carlos

 

[dodge20]

Here are 2 examples of a similar query I did, you can apply this to your tables. The second one will probably work better for you.

select * from awj_shop
where (name, address, zip) in
(select name, address_zip
from awj_shop
minus
select name, address_zip
from tcc_shop);

select * from awj_shop a
where not exists (select *
from tcc_shop t
where t.name = a.name
and t.address = a.address
and t.zip = a.zip);

Dodge20

 

[sunila7]

Hi,

Try these queries...



Select * from product
where ID not in
(SELECT Distinct PRodID from Stock where trdate>'01/01/03')



SELECT *
FROM product
WHERE NOT EXISTS
(SELECT *
FROM Stock
WHERE prodid = product.id
AND trdate >= '01/01/03')


Sunil

 

[CmPaiva]

Thanks to both of you. It works as expected!

Carlos