Not Equal To

[ainese]

I have a table with ESN and SERVICE TYPE and SUBSCRIBER TYPE. ESN can
appear more than once depending on the number of services associated.
Each service type has a charge.

To date I have the below code - where i'm trying to count esn where
service type is only = basic and not equal to data push or
tracking....


SELECT 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');


I added G.SERVICE <> "Data Push" but with the inner join I'm getting
some duplicates of 'Data Push' due to the
INNER JOIN


How do I omit them,


Can anyone advise me on how to do this please?


Thanks

 

[Bob Barrows]

I have a table with ESN and SERVICE TYPE and SUBSCRIBER TYPE. ESN can
appear more than once depending on the number of services associated.

Does that mean that the combination of ESN and SERVICE TYPE is unique? Or is
SUBSCRIBER TYPE also part of the unique key?
If that question is not clear, I'm asking if there can be multiple records
containing the same ESN, SERVICE TYPE and SUBSCRIBER TYPE.

Each service type has a charge.

Given the question you are asking below, I'm not sure why this is relevant
information. If it is relevant, please explain.

To date I have the below code - where i'm trying to count esn where
service type is only = basic and not equal to data push or
tracking....

Umm, if you select records where service type is only equal to "basic", how
can your resultset include records where it contains anything else?
Try it:
SELECT ESN, SERVICE FROM Data WHERE SERVICE="basic"

SELECT Count(F.ESN)

Can ESN contain Null? If not there is no point in doing Count(ESN) -
Count(*) will yield the same result.

AS ["Basic Activations"]

Brackets or quotes - you don't need both. I would go with the brackets ...
unless you want the quotes to be part of the column heading ...

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');


I added G.SERVICE <> "Data Push" but with the inner join I'm getting
some duplicates of 'Data Push' due to the
INNER JOIN

Oh. My. Word.

Why is the self-join needed?

Assuming the table is keyed by ESN, SERVICE and SUBSCR_TYP, this query
should give you the correct answer:

SELECT Count(*) As [Basic Activations] FROM Data
WHERE SERVICE="Basic" AND
SUBSCR_TYPE = "Service Activation"

If not, you need to show us some sample records where this query gives us
the incorrect answer, and tell us what the correct answer needs to be from
those sample records and why.