MySQL join gives duplicate records

[superaktieboy]

Hi
i have the following SQL tables:

prefix_bookings:
+---------+------------+-----------------+-------------+---------------+
| book_id | book_place | book_customerid | book_adults | book_children |
+---------+------------+-----------------+-------------+---------------+
|    3    |      1     |        1        |      2      |       2       |
|    42   |      1     |        1        |      2      |       2       |
|    41   |      1     |        1        |      1      |       1       |
|    40   |      1     |        2        |      1      |       1       |
|    39   |      1     |        2        |      1      |       1       |
|    38   |      1     |        2        |      1      |       1       |
|    37   |      1     |        2        |      1      |       1       |
|    36   |      1     |        1        |      2      |       2       |
|    35   |      1     |        1        |      2      |       2       |
+---------+------------+-----------------+-------------+---------------+


prefix_book_days:
+--------+--------------+------------+------------+
|bday_id | bday_book_id | bday_date  | bday_place |
+--------+--------------+------------+------------+
|   1    |      1       | 1222945199 |      1     |
|   2    |      1       | 1223031599 |      1     |
|   3    |      2       | 1221908399 |      1     |
|   4    |      2       | 1221994799 |      1     |
|   5    |      3       | 1222340399 |      1     |
|   6    |      4       | 1221735599 |      1     |
|   64   |      36      | 1222858799 |      1     |
|   63   |      36      | 1222426799 |      1     |
|   65   |      37      | 1221562799 |      1     |
|   66   |      39      | 1221476399 |      1     |
|   67   |      40      | 1222772399 |      1     |
|   68   |      41      | 1222167599 |      1     |
|   69   |      42      | 1221649199 |      1     |
+--------+--------------+------------+------------+

My MySQL query is this:

SELECT * 
FROM
	prefix_bookings as bookings
	JOIN prefix_booking_days as day
		ON bookings.book_id=day.bday_book_id

this works fine if the users wants to book something for only one day, but if i have multiple days for one booking (such as book_id 36, which has the book day ids: 64 and 63) it gives me the same booking (from prefix_bookings ) twice, example:

+---------+---------+
| book_id | bday_id |
+---------+---------+
|   3     |    5    |
|   36    |    64   |
|   36    |    63   |
+---------+---------+

i have also tried to just select it normally 'Select * FROM prefix_bookings as bookings, prefix_booking_ days as bdays WHERE...' but that didn't work either.

Can anyone please help me to only get one record per book id? i need only one 'bday_id' but not 2 'book_id'.

Thanks

[trochia]

[CODE=SQL]SELECT DISTINCT column FROM table[/CODE]

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Hope this helps.

[superaktieboy]

thanks for your reply,
i tried the following queries without any luck:

SELECT DISTINCT * 
FROM
	prefix_bookings as bookings,
	prefix_booking_days as day

and

SELECT DISTINCT * 
FROM
	prefix_bookings as bookings
	JOIN prefix_booking_days as day
		ON bookings.book_id=day.bday_book_id

thanks

[r035198x]

Don't do a DISTINCT * because all the rows are distinct anyway from that query. Rather put the distinct on book_id.

[superaktieboy]

they thanks for the reply.
following your advice, this is my new sql query:

SELECT DISTINCT bookings.book_id 
FROM
	prefix_bookings as bookings
	JOIN prefix_booking_days as day
		ON bookings.book_id=day.bday_book_id

but it still doesn't work..

thanks

[mwasif]

Use GROUP BY on book_id
[CODE=mysql]SELECT *
FROM
prefix_bookings as bookings
JOIN prefix_booking_ days as day
ON bookings.book_i d=day.bday_book _id
GROUP BY book_id[/CODE]

[superaktieboy]

hi, sorry for late reply, but that still didn't work, i also tried both the 'GROUP BY bookings.book_i d' and 'DISTINCT bookings.book_i d' in the same SQL as well as 'DISTINCT day.bday_book_i d' but still didn't work.

[wuka]

mysql join gives duplicate records

Have the same problem but solved it by using 'GROUP BY'. BUT, there's another problem - I have a big application so now I have to change all the 'buggy' code. The problem appeared last week after reinstalling OS and all the software on the server in my case. So I have a question - is it possible to tune mysql up to make this bug gone away?

[kovik]

Your question is vague and it has hijacked this poor 2-year-old thread. No one can solve a bug that you simply describe as "a bug." You have to explain what is going wrong, and you have to make your OWN thread.

Let's hop to it, eh?