extracting information outside a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

extracting information outside a table

Author

Topic

lambo99
Starting Member

4 Posts

Posted - 2008-02-22 : 08:36:18

Hi,

I have two tables - a stock table and a transaction table. The stock table is called stock and the transaction table is called trans. The table make a point of sale database. Both table have a joining field called prod_no.

I want to extract the stock line by querying stock.prod_no that are not present in the trans table for a period of time specified.

Basically, I want to find dead stock lines that have not been sold and, therefore, are not in the transaction table but that have a stock figure. I have to enter in a start and end date paramater using the ddate field in the trans table. I have looked at left, right and outer joins but with no luck and don't even know if this is the correct query type to use.

any help would be much appreciated,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-22 : 08:45:05

Try

Select columns from stock_table s
where not exists
(select * from transaction_table where prod_no =s.prod_no and ddate between @start and @end)

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-22 : 11:40:57

Alternativly:

SELECT 
	Stock.*
FROM
	Stock
LEFT OUTER JOIN
	Trans
	ON Stock.prod_no = Trans.prod_no
	AND Trans.TransDate BETWEEN @StartDate AND @EndDate
WHERE
	Trans.TransID IS NULL

lambo99
Starting Member

4 Posts

Posted - 2008-02-22 : 12:50:13

I will give these a try. thank you

lambo99
Starting Member

4 Posts

Posted - 2008-02-22 : 16:06:55

That worked beautifully my friend - thank you very much

quote:

Originally posted by Lamprey

Alternativly:

SELECT 
	Stock.*
FROM
	Stock
LEFT OUTER JOIN
	Trans
	ON Stock.prod_no = Trans.prod_no
	AND Trans.TransDate BETWEEN @StartDate AND @EndDate
WHERE
	Trans.TransID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources