I have theater functions which have a specific date time, so I am selecting them like this
SELECT
p.name, p.thumbnail, f.play
FROM `nj9tf_tlakdevtheater_functions` AS `f`
LEFT JOIN `nj9tf_tlakdevtheater_plays` AS `p`
ON f.play = p.id
WHERE
f.date >= "2013-11-12 19:48:51"
AND f.state = 1
AND p.state = 1
LIMIT 0 , 8
They come up fine but a certain play may have more than 1 function within a few days, the results will be used in a slider so repeated plays are not an option, right now I am filtering them once I get the results but that does not work very well for pagination, I been searching and found that COUNT( DISTINCT should do the trick, but when I use it only one record is returned, I am doing it like this
SELECT
p.name, p.thumbnail, COUNT(DISTINCT f.play)
FROM `nj9tf_tlakdevtheater_functions` AS `f`
LEFT JOIN `nj9tf_tlakdevtheater_plays` AS `p`
ON f.play = p.id
WHERE
f.date >= "2013-11-12 19:48:51"
AND f.state = 1
AND p.state = 1
LIMIT 0 , 8
I have something like this in the functions table
where all rows should be selected because of the date field is the one I am looking for
but rows 4, 6 and 10 should be excluded because in the matching records there is already one record of the same play
ion which case I should get back records 1, 2, 3, 5, 7, and 8
[table=“width: 500”]
[tr]
[td]ROW[/td]
[td]DATE[/td]
[td]PLAY[/td]
[/tr]
[tr]
[td]1[/td]
[td]2013-11-12 19:48:51[/td]
[td]3[/td]
[/tr]
[tr]
[td]2[/td]
[td]2013-11-12 19:48:51[/td]
[td]4[/td]
[/tr]
[tr]
[td]3[/td]
[td]2013-11-12 19:48:51[/td]
[td]5[/td]
[/tr]
[tr]
[td]4[/td]
[td]2013-11-12 19:48:51[/td]
[td]5[/td]
[/tr]
[tr]
[td]5[/td]
[td]2013-11-12 19:48:51[/td]
[td]6[/td]
[/tr]
[tr]
[td]6[/td]
[td]2013-11-12 19:48:51[/td]
[td]6[/td]
[/tr]
[tr]
[td]7[/td]
[td]2013-11-12 19:48:51[/td]
[td]7[/td]
[/tr]
[tr]
[td]8[/td]
[td]2013-11-12 19:48:51[/td]
[td]8[/td]
[/tr]
[tr]
[td]9[/td]
[td]2013-11-12 19:48:51[/td]
[td]9[/td]
[/tr]
[tr]
[td]10[/td]
[td]2013-11-12 19:48:51[/td]
[td]9[/td]
[/tr]
[/table]
any help is appreciated!!