CODEWITHSUNDEEP
mysqlduplicatesinner-join
RabbitBadger
RabbitBadger

Reputation: 589

Duplicate on MYSQL results when using inner join

I am trying to list two attributes(booking.roomno and room.price) with the condition that "booking.DATETO is not null" from two different tables.

Table: BookingBooking Table! Table:Room! enter image description here

I have tried using this command 

 select booking.roomno,room.price 
 from booking 
 inner join room on booking.ROOMNO=room.roomno 
 where booking.dateto is not null

although the return results came in with duplicated roomno and price like shown below enter image description here

Upvotes: 1

Views: 3453

Answers (2)

Valery
Valery

Reputation: 753

You have two bookings for the same room so the returned rows match your inner join. You seem to be trying to fetch all the rooms that have bookings. You would achieve that by adding DISTINCTROW before the selected fields.

select DISTINCTROW booking.hotelno, booking.roomno,room.price 
from booking 
inner join room on booking.ROOMNO=room.roomno AND
    booking.HOTELNO=room.HOTELNO
where booking.dateto is not null

Upvotes: 1

Peter Bowers
Peter Bowers

Reputation: 3093

room.roomno is not unique. It is only unique within a given hotel and your room table contains multiple hotels. You are going to have to specify hotelno in your join condition as well. Also since you might have multiple bookings for the same room (i.e., duplicates in booking table) you will need to do a DISTINCT to prevent that (but then you have to include the hotelno column in your field list):

select DISTINCT booking.roomno,room.price, room.hotelno
from booking 
inner join room on booking.ROOMNO=room.roomno 
   AND booking.hotelno=room.hotelno
where booking.dateto is not null

Upvotes: 4

Related Questions