'Not Equal to'

[ainese]

I have a table with ESN and SERVICE types,
Each ESN can have up to 3 service types. I am trying to select where
SERVICE Type is only equal to basic, but i get duplicates with the
below query.

Can anyone help with what code can be used for this type of query
please?


SELECT DISTINCT (Count(F.ESN)) AS ["Basic Activations"]
FROM Data AS F INNER JOIN Data AS G ON (F.ESN= G.ESN AND F.SERVICE=
"Basic" AND G.SERVICE <>"Data Push" AND G.SUBSCR_TYPE = "Service
Activation")
WHERE G.ESN NOT IN
(SELECT ESN FROM Data WHERE SERVICE = 'Tracking' );


thanks in advance,
Aine

 

[Douglas J Steele]

The various checks for values in the table should not be in the ON clause:

SELECT DISTINCT (Count(F.ESN)) AS ["Basic Activations"]
FROM Data AS F INNER JOIN Data AS G ON (F.ESN= G.ESN)
WHERE G.ESN NOT IN (SELECT ESN FROM Data WHERE SERVICE = 'Tracking' )
AND F.SERVICE= "Basic"
AND G.SERVICE <>"Data Push"
AND G.SUBSCR_TYPE = "Service Activation"


"ainese" wrote in message

I have a table with ESN and SERVICE types,
Each ESN can have up to 3 service types. I am trying to select where
SERVICE Type is only equal to basic, but i get duplicates with the
below query.

Can anyone help with what code can be used for this type of query
please?


SELECT DISTINCT (Count(F.ESN)) AS ["Basic Activations"]
FROM Data AS F INNER JOIN Data AS G ON (F.ESN= G.ESN AND F.SERVICE=
"Basic" AND G.SERVICE <>"Data Push" AND G.SUBSCR_TYPE = "Service
Activation")
WHERE G.ESN NOT IN
(SELECT ESN FROM Data WHERE SERVICE = 'Tracking' );


thanks in advance,
Aine