camnesia camnesia - 6 months ago 9
SQL Question

How to get SQL query HAVING count display BOTH records with the same value where INNER JOIN exists

This is the current query:

SELECT schedule.routenr, stops.stopname, schedule.scheduletime FROM schedule
INNER JOIN stops ON
schedule.id_stop=stops.id_stop
INNER JOIN tram ON
schedule.id_tram=tram.id_tram
WHERE tram.id_direction = '5' AND stops.stopname = 'Stourton' AND
schedule.scheduletime >= ('10:50:00')
OR tram.id_direction = '5' AND stops.stopname = 'CitySquare'

ORDER BY schedule.routenr ASC, schedule.scheduletime ASC;


These are the results displayed:

route_nr stopname scheduletime

1 CitySquare 09:57:00
2 Stourton 11:50:00
2 CitySquare 11:57:00
3 Stourton 12:50:00
3 CitySquare 12:57:00


Query Results with adding HAVING COUNT(schedule.routenr) > 1

route_nr stopname scheduletime
2 Stourton 11:50:00


I want to be able to display both records that have the duplicate count in route_nr so that stopname = Stourton and stopname = CitySquare are both displayed. This is what it should display.

route_nr stopname scheduletime

2 Stourton 11:50:00
2 CitySquare 11:57:00
3 Stourton 12:50:00
3 CitySquare 12:57:00


This is the list of Tables with data:

tram stops
id_tram id_direction id_stop stopname
1 1 1 GrimesDyke
2 1 2 SeacroftRingRoad
3 1 3 WykeBeck
4 1 4 FfordeGrene
5 2 5 St.James'sHospital
6 2 6 QuarryHill
7 2 7 Eastgate
8 2 8 CitySquare
9 2 9 Bodington
10 2 10 HeadingleyCentre
11 3 11 HydeParkCorner
12 3 12 UniversityofLeeds
13 3 13 Civic
14 4 14 Riverside
15 5 15 ClarenceDock
16 StJoseph's
17 ChurchStreet
18 Stourton
19 BalmRoad
20 BelleIsleNorth
21 BelleIsleCentral
22 BelleIsleSouth
23 MiddletonDistrictCentre
24 MiddletonCircus
25 Tingley

schedule
id_schedule id_tram id_stop routenr scheduletime
1 15 18 1 09:50:00
2 15 18 2 11:50:00
3 15 18 3 12:50:00
4 15 8 1 09:57:00
5 15 8 2 11:57:00
6 15 8 3 12:57:00
7 15 8 1 09:42:00
8 14 18 1 09:49:00
9 14 8 2 11:42:00
10 14 18 2 11:49:00

Answer

You could use this:

select tmp.routenr, maintable.stopname, maintable.scheduletime 
from(
    SELECT schedule.routenr, stops.stopname, schedule.scheduletime 
    FROM schedule 
    INNER JOIN stops 
        ON schedule.id_stop=stops.id_stop
    INNER JOIN tram 
        ON schedule.id_tram=tram.id_tram
    WHERE tram.id_direction = '5' AND  stops.stopname = 'Stourton' AND 
        schedule.scheduletime >= ('10:50:00') 
        OR tram.id_direction = '5' AND stops.stopname = 'CitySquare'
     ) maintable
inner join ( 
    SELECT schedule.routenr, count(*) as routenr_count
    FROM schedule 
    INNER JOIN stops 
        ON schedule.id_stop=stops.id_stop
    INNER JOIN tram 
        ON schedule.id_tram=tram.id_tram
    WHERE tram.id_direction = '5' AND  stops.stopname = 'Stourton' AND 
        schedule.scheduletime >= ('10:50:00') 
        OR tram.id_direction = '5' AND stops.stopname = 'CitySquare'
    group by schedule.routenr
    ) tmp
on tmp.routenr = maintable.routenr
where tmp.routenr_count > 1
ORDER BY maintable.routenr ASC, maintable.scheduletime ASC;

I wonder about or clause, could you confirm it is right? Because i think it should be something like

where (tram.id_direction = '5' AND  stops.stopname = 'Stourton' AND 
        schedule.scheduletime >= ('10:50:00') 
      )
      OR 
      (tram.id_direction = '5' AND stops.stopname = 'CitySquare'
      )

Editted: Changed so it work as your sample data. Another solution should be 2nd solution route_nr in (...) from Gordon Linoff (correct your intention of OR in his query first)

Comments