MYSQL COUNT issue

[SEOCoder]

Hi all,

This is my first post on this forum, I hope someone here can help because the last few forums were at a loss...

Any ways, I have this MYSQL command:

SELECT DISTINCT schedule_date, schedule_time, schedule_team_1, schedule_team_2, event_name, type_name, gen_duration, 
COUNT(DISTINCT((SELECT id FROM link_schedules WHERE schedule_id = schedules.schedule_id UNION SELECT id FROM 
link_events WHERE event_id = events.event_id))) as Count
FROM schedules 
LEFT JOIN events ON schedules.event_id = events.event_id
LEFT JOIN type ON schedules.type_id = ea_type.type_id 
WHERE schedules.schedule_date >= '2011-01-25' 
GROUP BY schedules.schedule_id 
ORDER BY schedules.schedule_date ASC, 
schedules.schedule_time ASC 
LIMIT 20

When I run this script I get the error message:

Subquery returns more than 1 row

[smantscheff]

Your problem is in the clause

COUNT(DISTINCT((SELECT id FROM link_schedules WHERE schedule_id = schedules.schedule_id UNION SELECT id FROM
link_events WHERE event_id = events.event_id))) as Count

I don't understand what you are aiming at. Cut out this clause and reconstruct it from scratch. What is it that you want to count?

[SEOCoder]

What I need to count is 'id' from two separate tables. They are both related to schedules - 'link_schedules' is a table of ids where the individual schedule has been selected by a user - 'link_events' is a table of ids where an event has been selected that covers many schedules.

An example would be, 'liverpool v everton', 'arsenal v celtic'. Both are schedules, but both would come under the event 'Premier League'. So 'link_schedules' would be users that selected 'liverpool v everton' OR 'arsenal v celtic' and 'link_events' would be users that selected 'Premier League'.

I have a schedule, lets say its called 225, the schedule will have an event called 15. Now if I want to get the number of ids that have selected to view schedule 225 then I need to count the number of ids from link_schedules where schedule = 225 AND count the number of ids from link_events where event = 15.

There is a possibility that an id that appears in the schedule list may appear in the event list, so I need to make it DISTINCT to eliminate duplicates.

I hope that clears up some confusion. I have broken the code down and tried different ways of approaching the problem, but this is the closest I have got to a solution.

[smantscheff]

It's still quite confusing.
If I get it right, you have the tables
- schedules
- events
- link_schedules
- link_events
with link_schedules and link_events having an ID field which denotes the user and a schedule_id resp. events_id field which links to the schedules resp. events table.
Also the schedule may link to an event with an event_id field.
If this is correct, then you can find out about the number of users (ids) linked to a schedule with the id XXX with this query (not tested):

select count(distinct(id)) from
(
 (select id from link_schedules ls where ls.schedule_id=XXX)
 union
 (
 select id from link_events le 
 join events e on le.event_id=e.event_id 
 join schedules s on s.event_id=e.event_id
 where s.schedule_id=XXX
 )
)

[SEOCoder]

Thanks for the response.

I tried the code, I had to change it slightly. It worked by itself, but when it was integrated into the main MYSQL line it failed. I believe the problem is there being two 'FROM' statements. I attempted using extra brackets to no avail.

SELECT count(distinct(id)) from
(
 (select id from link_schedules where schedule_id = 144)
 union
(select id from link_events where event_id = 15)
) AS counted

SELECT DISTINCT schedule_date, schedule_time, schedule_team_1, schedule_team_2, event_name, type_name, gen_duration, 
count(distinct(id)) from
(
(select id from link_schedules where schedule_id = schedules.schedule_id)
 union
(select id from link_events where event_id = events.event_id)
) AS counted
FROM schedules 
LEFT JOIN events ON schedules.event_id = events.event_id
LEFT JOIN type ON schedules.type_id = ea_type.type_id 
WHERE schedules.schedule_date >= '2011-01-25' 
GROUP BY schedules.schedule_id 
ORDER BY schedules.schedule_date ASC, 
schedules.schedule_time ASC 
LIMIT 20

[SEOCoder]

This works, except that events.event_id and schedules.schedule_id do not exist yet because their tables have not yet been included. I am struggling to put the code behind the rest of the tables ...

SELECT DISTINCT schedule_date, schedule_time, schedule_team_1, schedule_team_2, event_name, type_name,
gen_duration,
count(distinct(counted.id)) from
(
(select id from link_schedules where schedule_id = schedules.schedule_id)
 union
(select id from link_events where event_id = events.event_id)
) AS counted, schedules 
LEFT JOIN events ON schedules.event_id = events.event_id
LEFT JOIN type ON schedules.type_id = type.type_id 
WHERE schedules.schedule_date >= '2011-01-25' 
GROUP BY schedules.schedule_id 
ORDER BY schedules.schedule_date ASC, schedules.schedule_time ASC LIMIT 20