Select but exclude repeated records

Databases
1

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!!

2

whenever you use an aggregate function like COUNT(*) along with other columns in the SELECT clause, those other columns also have to be in the GROUP BY clause

also, if you write LEFT OUTER JOIN but then put filtering conditions on the right table into the WHERE clause, the query behaves like an inner join… those conditions need to go into the ON clause of the LEFT OUTER JOIN

3

Okay so I made as you mentioned but I am getting a record with null values, all the columns, and the play column now has a value of 1 for all of them althoug they all have different value ids, below is my query where by the way I am getting all the expected records except 1 which I am guessing is that of null values, I am checking to see if there is any wrong record in the database, thanks for your help!!


SELECT
p.name, p.thumbnail, p.id, COUNT(DISTINCT f.play)
FROM `theater_functions` AS `f`
LEFT JOIN `theater_plays` AS `p`
ON f.play = p.id
AND p.state = 1
AND f.state = 1
WHERE
f.date >= "2013-11-12 19:48:51"
GROUP BY
f.play
LIMIT 0 , 8
4

I am actually getting the correct records, there was an issue with the null values row I was getting, thank you so much for your help!!