If Statement in SQL 2000

[Djbell]

Hi All

I am new to SQL 2000 after coming from Access DBs, I am trying to create a query that is based on two tables.

Tabel1 = ProductionMain
Table2 = ProductionDowtime

Both these tables have a field called ProdcutionID, this is the primary key field for the prodcutionMain table.

What I want to do is display all records from ProductionMain where ProductionMain.ProductionID <> ProductionDowntime.ProductionID

Now usualy in Access all I would do is enter the following line in the criteria box '<> ProductionDowntime.ProductionID' this would display all records from ProductionMain as long as the ProductionID wasnt in the ProductionDowntime table. I tried the same in SQL but it just gives me errors.

Any help is appreciated.

Regards

Djbell

 

[nickdel]

DjBell, this should do what you require.

select pm.*
from ProductionMain pm 
where not pm.ProductionID in(select pd.ProductionID from ProductionDowntime pd)

Cheers

Nick

 

[ca8msm]

Nick,

That could be quite a slow method of retrieving the records (as opposed to using a join) and there may be issues if any records have null values in the id field. There is also a mistake in the code as the "not" should appear after "pm.ProductionID".

Instead, I'd go with a join such as:

[COLOR=green]-- Sample Tables
[/color][COLOR=blue]declare[/color] @productionmain [COLOR=blue]table[/color] (productionid [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), prodname [COLOR=blue]varchar[/color](10))
[COLOR=blue]declare[/color] @productiondowntime [COLOR=blue]table[/color] (id [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), productionid [COLOR=blue]int[/color])

[COLOR=green]-- Sample Data
[/color][COLOR=blue]insert[/color] @productionmain [COLOR=blue]values[/color] ([COLOR=red]'mark'[/color])
[COLOR=blue]insert[/color] @productionmain [COLOR=blue]values[/color] ([COLOR=red]'dave'[/color])
[COLOR=blue]insert[/color] @productiondowntime [COLOR=blue]values[/color] (1)

[COLOR=green]-- Show the results from the main table
[/color][COLOR=blue]select[/color] productionid, prodname [COLOR=blue]from[/color] @productionmain

[COLOR=green]-- Show the results from the downtime table
[/color][COLOR=blue]select[/color] id, productionid [COLOR=blue]from[/color] @productiondowntime

[COLOR=green]-- Query to show all the records in the main table that don't exist in the downtime table
[/color][COLOR=blue]select[/color] m.productionid, m.prodname
[COLOR=blue]from[/color] @productionmain m
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] @productiondowntime [COLOR=blue]d[/color] [COLOR=blue]on[/color] m.productionid = [COLOR=blue]d[/color].productionid
[COLOR=blue]where[/color] [COLOR=blue]d[/color].productionid [COLOR=blue]is[/color] null

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bborissov]

SELECT ProductionMain.*
       FROM ProductionMain
LEFT JOIN ProductionDowntime ON ProductionMain.ProductionID =
                                ProductionDowntime.ProductionID
WHERE ProductionDowntime.ProductionID IS NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Djbell]

Perfect!!!

Cheers mate.

Djbell