SQL Syntax - having clause?

Question

I should know this - but I don't, can anyone help me with a syntax plz.

Let's say I'm tracking runners in a cross-country relay race, I've got a main join table that associated runners with races... and a runners table that tells me where/when they started their leg and where/when they ended... there are also check-points that are logged along the route.

It's a requirement that each runner have a start and end point - HOWEVER, this is not always being entered correctly* - I need a query to pull out runners who DO NOT have BOTH types of entries in a race.

(*yes I am fixing the data entry side of things)

Thx

STRUCTURE:

RACE TABLE 
 raceID (int)
 runnerID (int)

STOPS TABLE
 runnerID (int)
 stopTypeID (int)  -- fk to stop type
 when (timestamp)
 sequence (smallint)

I don't really "have" anything yet- because I'm stymed by what I need...

the basic Q to get runners and race logs is this

SELECT *
FROM RACES R
JOIN STOPS S ON S.runnerID = R.runnerID
WHERE R.RaceID = 133



RESULTS
1 = start 
2 = check point
3 = rest point
4 = end

RACEID  RUNNERID    STOPTYPEID
133     21      1 
133     21      4
133     21      3
133     21      2
133     21      2
133     21      2
133     21      2
133     21      2
133     23      2
133     23      2
133     23      2
133     23      2
133     23      4

Notice runner 23 is missing a type '1' (start) I want a list of Runners in a race that are missing data... If i say 1's and 4's are REQUIRED....

(thx and sorry for not posting the 'data' needed)

Can you show us what you have? what you expect? And what you have tried? — Arion, Mar 28 '12 at 13:46
not both.. would that be `WHERE (field1 IS NOT NULL OR field2 IS NOT NULL)`? — Konerak, Mar 28 '12 at 13:48

score 1 · Answer 1 · answered Mar 28 '12 at 13:50

Slight guess as to your structure, but:

SELECT RR.RunnerId
FROM Race AS R
INNER JOIN RaceRunners AS RR
  ON <whatever>
LEFT OUTER JOIN Runners AS Starters
  On Starters.ID = RR.RunnersID
  AND Starters.Type = 'StartPoint' -- don't know how you're specifying this.
LEFT OUTER JOIN Runners AS Finishers
  On Finishers.ID = RR.RunnersID
  AND Finishers.Type = 'EndPoint' -- don't know how you're specifying this.
WHERE Starters.<Whatever> IS NULL
OR Finishers.<Whatever> IS NULL

Taryn · Answer 2 · 2012-03-28T14:58:02.970

You can do this way to get the number of stops for each person in your two stop types (start, end), this also gives you a column showing if the runner had a start or end check-in.

create table #race
(
    raceid int,
    runnerid int
)
create table #stops
(
    runnerid int,
    stoptypeid int,
)

insert into #race values(133, 21)
insert into #race values(133, 23)
insert into #race values(133, 20)
insert into #race values(133, 33)

insert into #stops values(21, 1)
insert into #stops values(21, 4)
insert into #stops values(21, 3)
insert into #stops values(21, 2)
insert into #stops values(21, 2)
insert into #stops values(21, 2)
insert into #stops values(21, 2)
insert into #stops values(21, 2)
insert into #stops values(23, 2)
insert into #stops values(23, 2)
insert into #stops values(23, 2)
insert into #stops values(23, 2)
insert into #stops values(23, 4)

insert into #stops values(20, 3)
insert into #stops values(20, 1)

insert into #stops values(33, 4)
insert into #stops values(33, 1)


    SELECT r.raceid
        , r.runnerid
        , (select COUNT(*) 
            FROM #stops s
            WHERE S.runnerID = R.runnerID
                AND s.stoptypeid IN (1, 4)) as StartEndStops
    , CASE WHEN (SELECT COUNT(*) FROM #stops s WHERE S.runnerID = R.runnerID AND s.stoptypeid = 1) = 1 
            THEN 'Start Check-in'
            ELSE ''
        END as 'Start'
    , CASE WHEN (SELECT COUNT(*) FROM #stops s WHERE S.runnerID = R.runnerID AND s.stoptypeid = 4) = 1
            THEN 'End Check-in'
            ELSE ''
        END as 'End'
    FROM #race R    

drop table #race
drop table #stops

Results:

RaceId    RunnerId  StartEndStops     Start             End
133       21         2            Start Check-in    End Check-in
133       23         1                              End Check-in
133       20         1            Start Check-in
133       33         2            Start Check-in    End Check-in

score 0 · Answer 3 · answered Mar 28 '12 at 15:28

I ended up using this - although I'm still curious if it was the BEST solution... I still equate 'verbose' with 'inefficient' - and I'm not sure that's a good mind set. I just figure if it's cluttered and such in code than there has to be a more elegant way of stating it that the SQL engine will "like" better..

of course - THIS IS ABSTRACTED - there is a LOT more filtering going on in the Q that runs - I chopped this down to a bare min for ease of 'concept' - Thanks

SELECT DISTINCT RaceID, RunnerID 
FROM (
  SELECT 
     raceID
    ,runnerID
    ,(
    SELECT count(stops.ID)
      FROM races
      JOIN stops
     WHERE raceid = main.raceID
       AND STOPTYPEID = 1
) AS poStart
,(
    SELECT count(stops.ID)
      FROM races
      JOIN stops
     WHERE raceid = main.raceID
       AND STOPTYPEID = 4
) AS poEnd


FROM races

WHERE x BETWEEN '2012-03-01 00:00:00.0' AND '2012-03-31 23:59:59.9'

ORDER BY race
) AS T1 WHERE poStart = 0 OR poEnd = 0

SQL Syntax - having clause?

3 Answers3