CODEWITHSUNDEEP
mysqldistinctdistinct-values
Alex
Alex

Reputation: 1309

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.

Upvotes: 0

Views: 41

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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.

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try below - using a case when expression

SELECT DISTINCT `booking_id`, `booking_ticket`, `booking_price`, `bookingcomment_id`, 
case when `bookingcomment_message`<>'0' then 'No' else 'Yes' end as comments
FROM `mysystem_booking`
LEFT JOIN `mysystem_bookingcomment` ON `mysystem_booking`.`booking_id` = `mysystem_bookingcomment`.`bookingcomment_link`
WHERE `booking_id` = 29791

Upvotes: 0

Related Questions