SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    select but exclude repeated records

    I have theater functions which have a specific date time, so I am selecting them like this

    Code MySQL:
    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

    Code MySQL:
    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

    ROW DATE PLAY
    1 2013-11-12 19:48:51 3
    2 2013-11-12 19:48:51 4
    3 2013-11-12 19:48:51 5
    4 2013-11-12 19:48:51 5
    5 2013-11-12 19:48:51 6
    6 2013-11-12 19:48:51 6
    7 2013-11-12 19:48:51 7
    8 2013-11-12 19:48:51 8
    9 2013-11-12 19:48:51 9
    10 2013-11-12 19:48:51 9

    any help is appreciated!!
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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!!

    Code MySQL:
    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
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.

  4. #4
    SitePoint Addict tlacaelelrl's Avatar
    Join Date
    Apr 2011
    Location
    Mexico city, Mexico
    Posts
    353
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!
    Do you get bothered because I do the same thing every day?
    Do you question why I do it?
    Then find something that you actually like doing!!!

    Stop thinking on what I do.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •