count occurances in sql

Hi,

I am trying to count the times a machine has been scanned in to a table. what happens is everytime a product is read at a station it is entered in to the table with the following data

stationid
modelnumber
serialnumber

i want to establish a list of every machine that has been read at station id 15 but not station id 2 or 5, does this make sense? basically to establish what machines are not being read at points a and b but end up at point c.

obviously to do this i need to check the model and serials throughout the table to see if there is another occurance of it against station 2 or 5.

i have done this before but cannot recreate it for the lfie of me!

all help is greatly welcomed :)

Quote:

Originally Posted by 692909824

Hi,

I am trying to count the times a machine has been scanned in to a table. what happens is everytime a product is read at a station it is entered in to the table with the following data

stationid
modelnumber
serialnumber

i want to establish a list of every machine that has been read at station id 15 but not station id 2 or 5, does this make sense? basically to establish what machines are not being read at points a and b but end up at point c.

obviously to do this i need to check the model and serials throughout the table to see if there is another occurance of it against station 2 or 5.

i have done this before but cannot recreate it for the lfie of me!

all help is greatly welcomed :)

Your script should be:

SELECT *
FROM [TABLE]
WHERE serialno NOT IN
(SELECT serialno
FROM [TABLE]
WHERE stationid = 2 or stationid = 5)
ANDstationid =15