Duplicate on MYSQL results when using inner join

Question

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: Booking Booking 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

You're not specifying the type in your query, so it's joining with all the types. I don't know why it's giving the same price for all of them, though. — Barmar
– Barmar, Commented Apr 19, 2015 at 7:28

Peter Bowers · Accepted Answer · 2015-04-19 07:31:43Z

4

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

edited Apr 19, 2015 at 7:31

answered Apr 19, 2015 at 7:28

Peter Bowers

3,1031 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barmar Over a year ago

Also the room type: single, double, etc.

Peter Bowers Over a year ago

I think if you look closely at the room table you'll see that the hotelno is the key we're interested in, and not the type...

Barmar Over a year ago

Yeah, didn't notice that the type is different depending on the hotel.

Valery · Accepted Answer · 2015-04-19 07:38:00Z

1

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

edited Apr 19, 2015 at 7:38

answered Apr 19, 2015 at 7:26

Valery

7537 silver badges22 bronze badges

2 Comments

Barmar Over a year ago

This will still give duplicate roomno when the price is different.

Valery Over a year ago

Agreed. Take a look at Peter Bowers' reply as well. He noted that there may be the same room number in multiple hotels. Edited my answer to correspond to that.

Collectives™ on Stack Overflow

Duplicate on MYSQL results when using inner join

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related