prevent duplicate result when using self-join in mysql

Question

I have some hotel with different hotel view for each room , and my end user when want 3 rooms for example each the room is 2 bed I have to combination the record for that , some times some fields may be duplicated and It's not important , and the user may have different room type in this situation I make example to each room have 2 bed number : I have problem when get result : I use self-join in "mysql" and all thing are true but some problem is have same row in this situation this is my code:

    SELECT
   table1.id,
   table2.id,
   table3.id,
   table1.num_bed,
   table2.num_bed,
   table3.num_bed 
   LEFT JOIN
      tour_package table2 
      ON table1.tour_id = table2.tour_id 
      AND table1.hotel_id = table2.hotel_id 
      AND table1.start_date = table2.start_date 
   LEFT JOIN
      tour_package table3 
      ON table2.tour_id = table3.tour_id 
      AND table2.hotel_id = table3.hotel_id 
      AND table2.start_date = table3.start_date 
WHERE
   table1.num_bed = 2 
   AND table2.num_bed = 2 
   AND table3.num_bed = 2

the result is :

Please attention to id , the one is table1.id, two is table2.id and three is table3.id

In the result we have some result like : 1-2-1 1-1-2 and etc
I want to prevent this and have one of them , please help me

Here's the problem: In your data you showed us, the values all happen to be the same for the "duplicates," but in reality this might not be the case. So, you will need to give us logic for which one of the "duplicate" rows should be shown. And it would help your question to not show contrived data where the values are always the same. — Tim Biegeleisen, Jul 23 '18 at 06:12
apply a rank and select any one of your id's. That would solve the problem. — Aritra Bhattacharya, Jul 23 '18 at 06:14
The query in the question would produce a syntax error, not the output described. There is no from clause. — Shadow, Jul 23 '18 at 06:22
@TimBiegeleisen yes when you use self-join there are lots of duplicate filed but my point is to id of each reacord I said above — mohammad zahedi, Jul 23 '18 at 06:25
@AritraBhattacharya could help more? example or a link to research — mohammad zahedi, Jul 23 '18 at 06:26
@Shadow I don't have syntax error and the result show correctly — mohammad zahedi, Jul 23 '18 at 06:27
@mohammadzahedi.. you can refer to this thread.https://stackoverflow.com/questions/3333665/rank-function-in-mysql — Aritra Bhattacharya, Jul 23 '18 at 06:28
Your actual query may not have a syntax error but the one in your question does. As I wrote, there is no from clause in the query. — Shadow, Jul 23 '18 at 06:30
You're not joining on the key `table_n.id`, so you get "extra" rows. To get the "exact" number of row you have ho join on a key — DDS, Jul 23 '18 at 07:24
@DDS I just use this to you understand what is my idea about this, I have to combination my record to each other where they have one hotel_id and etc , because some user what three room with different views and num_bed for one hotel in one time — mohammad zahedi, Jul 23 '18 at 07:27
this wasn't clear at time of my comment... you were just asking about left join — DDS, Jul 23 '18 at 08:58
Hi. "result like"--Like what? What is that an example of? "I want to prevent this"--Prevent what? "one of them"--Which one? What exactly do you want? Use language to clearly explain what you mean. Explain how the desired output should be as a function of input tables. When does a row belong in the output? Give simple example inputs & desired outputs. Please read & act on [mcve]. That includes a specification, explanation & cut & pastable code & text, not images/links, for text (including tables). — philipxy, Jul 26 '18 at 05:45

Shadow · Answer 1 · 2018-07-23T07:15:29.953

I assume the goal of the query is to list up to 3 rooms per record in the same hotel, for the same tour and dates that have 2 bed.

(I honestly do not get the point of the query because it will list all 2-bed rooms in the tour_package table.)

This means that not only 1-1-2 and 1-2-1 are duplicates, but also the 2nd 1 is a redundant information. In hotel no. 7 there are only 2 rooms that satisfy this criteria: 1 and 2.

In the join criteria I would require that a record with different id should be returned from each of the tables. This would force the query to return list of unique ids in a record.

SELECT
   table1.id,
   table2.id,
   table3.id,
   table1.num_bed,
   table2.num_bed,
   table3.num_bed 
FROM tour_package table1
LEFT JOIN
      tour_package table2 
      ON table1.tour_id = table2.tour_id 
      AND table1.hotel_id = table2.hotel_id 
      AND table1.start_date = table2.start_date
      AND table1.id<table2.id 
LEFT JOIN
      tour_package table3 
      ON table2.tour_id = table3.tour_id 
      AND table2.hotel_id = table3.hotel_id 
      AND table2.start_date = table3.start_date
      AND table2.id<table3.id 
WHERE
   table1.num_bed = 2 
   AND table2.num_bed = 2 
   AND table3.num_bed = 2

However, the above query can potentially still return redundant data if there are at least 2 rooms with the above criteria in a hotel. Assuming the 2 rooms have the ids 1 and 2, the query would return 1, 2, null and 2, null, null. To overcome this issue, I would just simply write:

select id, hotel_id from tour_package
where tour_package.num_bed=2
order by tour_id, hotel_id, start_date

The reason: even your on query will show all 2-bed rooms in your tour_package table.

thanks, you understand some part of my code , now I explaining for you , I have some hotel with different view for each room , and my end user when want 3 rooms for example each the room is 2 bed I must combination the record for that , ye some times some fields may be duplicated and It's not important , and the user may have different room type in this situation I make example to each room have 2 bed number and I don't understand your query — mohammad zahedi, Jul 23 '18 at 07:20
And I do not understand your comment. There was nothing in your original question or code about views and room types. It may be simpler if you removed this question completely and asked another one with all the necessary background information and data structure included. It may be worthwhile to show your question to somebody before posting it to SO to get the English part right. — Shadow, Jul 23 '18 at 10:50

prevent duplicate result when using self-join in mysql

1 Answers1