Search code examples
mysqldistinctdistinct-values

MySQL DISTINCT returning not so distinct results

Good day,

I have a small issue with MySQL Distinct. Trying the following query in my system :

SELECT DISTINCT `booking_id`, `booking_ticket`, `booking_price`, `bookingcomment_id`, `bookingcomment_message` FROM `mysystem_booking`
LEFT JOIN `mysystem_bookingcomment` ON `mysystem_booking`.`booking_id` = `mysystem_bookingcomment`.`bookingcomment_link`
WHERE `booking_id` = 29791

The point is that there are bookings like 29791 that have many comments added. Let's say 10. Then when running the above query I see 10 results instead of one.

And that's not the way DISTINCT supposes to work. I simply want to know if there are any comments. If the comment ID is not 0 then there is a comment. Of course I can add COUNT(blabla) as comment_number but that's a whole different story. For me now I'd like just to have this syntax right.

Solution

  • You may try aggregating here, to find which bookings have at least a single comment associated with them:

    SELECT
    b.booking_id,
    b.booking_ticket,
    b.booking_price
FROM mysystem_booking b
LEFT JOIN mysystem_bookingcomment bc
    ON b.booking_id = bc.bookingcomment_link
WHERE
    b.booking_id = 29791
GROUP BY
    b.booking_id
HAVING
    COUNT(bc.bookingcomment_link) > 0;

    Note that depending on your MySQL server mode, you might have to also add the booking_ticket and booking_price columns to the GROUP BY clause to get the above query to run.