How to ignore lines from tbl2 while main table is tbl1 in sql?

Visit
tmstamp,   trackno, qno, service
01/01/2011,1,       01,  Sv1
01/01/2011,2,       03,  Sv1
01/01/2011,3,       04,  Sv2
01/01/2011,4,       06,  Sv2
01/02/2011,1,       01,  Sv1

Matter
tmstamp,   trackno, code
01/01/2011,1,       a
01/01/2011,1,       b
01/01/2011,1,       e
01/01/2011,2,       c
01/01/2011,2,       b
01/01/2011,3,       c
01/01/2011,3,       a
01/01/2011,4,       d
01/01/2011,4,       c
01/02/2011,1,       a
01/02/2011,1,       b

select service, count(qno) as 'qno_served' from visit group by service

service, qno_served
SV1,     3
SV2,     2

My current statement is how many qno they have for each service.

I want to twist my statement so that I will link to matter table. And check that whether each qno has a code "a".

If a qno has a code "a", I don't want to count in.

So, my final result will b linke this.

service, qno_served
SV1,     1
SV2,     1

Shall I use join or wht me开发者_开发技巧thod to get this?

Try:

SELECT Service, COUNT(qno) AS [qno_served]
  FROM Visit
 WHERE NOT EXISTS (
    SELECT * FROM Matter WHERE Matter.TrackNo = Visit.TrackNo AND Matter.code = 'a')
 GROUP BY Service

SELECT 
 V.service
,COUNT(1) AS qno_served

FROM MyDatabase.dbo.Visit AS V

LEFT JOIN (
    SELECT DISTINCT
    M.trackno

    FROM MyDatabase.dbo.Matter AS M

    WHERE M.code = 'a'
) AS T
    ON V.trackno = T.trackno

WHERE T.Trackno IS NULL

GROUP BY V.service

ORDER BY service