MySQL JOIN 2 row

Question

I have tables as below:

Suits

| id | hotel_id |   name   |  etc ...
  1        3      Some name
  2        3      SomeName2

Orders

| id | suits | etc ...
   1    1,2

I want to get order list from database with suit names as below:

| id | hotel_id |        suits       | etc ...
   1      3      Some name, SomeName2

I'm trying with this code:

            SELECT
            o.id,
            h.name as hotel,
            o.hotel as hotel_id,
            s.name
            FROM h_orders o
            LEFT JOIN hotels h ON o.hotel = h.id
            LEFT JOIN suits s ON o.suits = s.id
            WHERE o.user_id = ? GROUP BY o.id ORDER BY o.id DESC

But getting only first suit name. How can I get result as mentioned above? Thanks in advance.

Normalize the schema. Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|191.1113) — sticky bit, Mar 22 '20 at 16:49
if you have 'have tables as below' then this 'LEFT JOIN hotels h ON o.hotel = h.id' is not possible. — P.Salmon, Mar 22 '20 at 16:51

score 1 · Answer 1 · answered Mar 22 '20 at 17:01

1

You can do it with the function FIND_IN_SET():

SELECT 
  o.id,
  h.name as hotel,
  h.id as hotel_id,
  group_concat(s.name) as suits
FROM h_orders o 
LEFT JOIN suits s ON o.suits = s.id
LEFT JOIN hotels h ON o.hotel = h.id
WHERE o.user_id = ? AND FIND_IN_SET(s.id, o.suits)
GROUP BY o.id, h.name, h.id 
ORDER BY o.id DESC

I kept the condition o.user_id = ? in the query although you did not explain what it is needed for.

answered Mar 22 '20 at 17:01

forpas

160,666
10
38
76

Thank you. But unfortunately, doesn't worked. Same as first query, returns all data with only first suit name. – Rabil Aliyev Mar 22 '20 at 17:15
Do you need the join of the table `hotels`? You did not post sample data for it. It would be better if you created a fiddle here:https://www.db-fiddle.com with data so I can test, – forpas Mar 22 '20 at 17:35

score 1 · Accepted Answer · answered Mar 22 '20 at 18:06

First, you should fix your data model. Storing numeric ids in a column is bad for many reasons:

Numbers should not be stored as strings.
Strings should not be used to store multiple values.
Ids should have properly declared foreign key relationships.
SQL has poor string handling functionality.
SQL has a great way to store lists. It is called a table, not a column.

Sometimes, we are stuck with other people's really, really, really bad decisions. If that is the case, you can do what you want using find_in_set().

Because you are using left joins, I assume that you want all rows even when there is no match. That suggests:

SELECT o.id, o.hotel as hotel_id, GROUP_COCNAT(s.name SEPARTOR ', ') as suits
FROM h_orders o LEFT JOIN
     hotels h
     ON o.hotel = h.id LEFT JOIN
     suits s
     ON FIND_IN_SET(s.id, o.suits) > 0
WHERE o.user_id = ?
GROUP BY o.id, o.hotel
ORDER BY o.id DESC;

However, you should put your focus into fixing the data model.

Also, it is not clear why you would have a LEFT JOIN from orders to hotels. Is it possible to have an "order" without a valid "hotel"?

MySQL JOIN 2 row

2 Answers2