CODEWITHSUNDEEP
sqlselectjoincount
Shane
Shane

Reputation: 325

MySQL SELECT with join, count, order by

I am trying to join two tables as follows:

tours                                 tours_schedules
---------------------                 ---------------------------------
tour_id | title     |                 schedule_id | tour_id | startdate
---------------------                 ---------------------------------
1       | Day Trip 1                  1           | 1       | 2014-05-23
2       | Day Trip 2                  2           | 1       | 2014-05-24
                                      3           | 2       | 2014-06-01
                                      4           | 1       | 2014-05-17

I want to select results that will tell me the tours.tour_id, tours.title, the count of records in tours_schedules where tours_schedules.tour_id matches tours.tour_id and the date of the earliest matching tours_schedules.startdate.

The results should look like this

results
---------------------------------------------------------------
tour_id  | title         | schedule_count | earliest_startdate 
---------------------------------------------------------------
1        | Day Trip 1    | 3              | 2014-05-17
2        | Day Trip 2    | 1              | 2014-06-01

I can't get it right, can anyone put me out of my misery?! The following are among the many I've tried. Counting the tour_schedules seems to stop more than one row in tours being selected. 

SELECT DISTINCT  t.title, a.startdate, COUNT (DISTINCT a.startdate) AS schedule_count
FROM tours t
INNER JOIN tours_schedules a 
ON t.tour_id = a.tour_id
WHERE t.category_id = 1
ORDER BY a.startdate ASC


SELECT DISTINCT  t.title
FROM tours t
RIGHT JOIN tours_schedules a
ON t.tour_id = a.tour_id
ORDER BY a.startdate


SELECT a.schedule_id, COUNT(a.schedule_id) AS schedule_count, t.title
FROM tours_schedules a
JOIN tours t
ON a.tour_id = t.tour_id
ORDER BY a.startdate

SELECT title, tour_id FROM tours
UNION ALL
SELECT startdate FROM tours_schedules

SELECT t.title, t.tour_id, (SELECT a.startdate)
FROM tours t
JOIN tours_schedules a
WHERE t.tour_id = a.tour_id 

SELECT t.title, t.tour_id, (SELECT a.startdate LIMIT 1), (SELECT COUNT(a.startdate) WHERE t.tour_id = a.tour_id)
FROM tours t
JOIN tours_schedules a
WHERE t.tour_id = a.tour_id

Upvotes: 2

Views: 77

Answers (1)

StanislavL
StanislavL

Reputation: 57421

SELECT a.schedule_id, 
       COUNT(a.schedule_id) AS schedule_count, 
       MIN(a.startdate) AS earliest_startdate , 
       t.title
FROM tours_schedules a
JOIN tours t
ON a.tour_id = t.tour_id
GROUP BY t.tour_id

Upvotes: 2

Related Questions