Mysql join query multiple values

Question

I have a table called facility. Structure looks as follows:

id | name
---------
1 | Hotel
2 | Hospital
3 | medical shop

I have an other table which is taking data from the above table and keeping multiple values in one column. View looks like below:

id | facilities
---------------
1 | Hospital~~medical shop~~Hotel
2 | Hospital~~Hotel
3 | medical shop~~Hotel

If I want to join these two tables how does the query look like?

I tried this, but it didn't work:

select overview.facilities as facility 
from overview join facility on facility.id=overview.facilities;

I basically want to display the values in the second table in a list and when i try editing the form i should be able to distinguish values that are selected(which is fetched from second table) and those which are not selected(which is fetched from first table). — Varuni N R
– Varuni N R, Commented Dec 28, 2015 at 19:02
There is no relation in both table u need to add a column for making relation — devpro
– devpro, Commented Dec 28, 2015 at 19:32
Obviously there is nothing linking the two tables. I suggest you take the time to learn about joins and foreign keys. — Kristoffer Bohmann
– Kristoffer Bohmann, Commented Dec 28, 2015 at 20:42

John Ruddell · Accepted Answer · 2015-12-28 19:38:24Z

you can do this with a bit of hackery

select o.facilities as facility 
from overview o 
join facility f on find_in_set(f.facilities, replace(o.facilities, '~~', ','));

I would highly recommend you change the way you are storing data. currently it is considered un normalized and that quickly becomes a monster to deal with

you should change your table structure to look something more like this

+----------+--------------+
|       facility          |
+----------+--------------+
|     id   |  name        |
+----------+--------------+
|     1    | Hotel        |
|     2    | Hospital     |
|     3    | medical shop |
+----------+--------------+

+-----------+-------------+
|        overview         |
+-----------+-------------+
|    id     | facility_id |
+-----------+-------------+
|     1     |      2      |
|     2     |      3      |
|     3     |      1      |
|     4     |      2      |
|     5     |      1      |
|     6     |      3      |
|     7     |      1      |
+-----------+-------------+

Code Explanation:

basically you are wanting to find the matching facilities in the overview. one handy function MySQL has is FIND_IN_SET() that allows you to find an item in a comma separated string aka find_in_set(25, '11,23,25,26) would return true and that matching row would be returned... you are separating your facilities with the delimiter ~~ which wont work with find_in_set... so I used REPLACE() to change the ~~ to a comma and then used that in the JOIN condition. you can go from here in multiple ways.. for instance lets say you want the facility id's for the overview.. you just add in the select GROUP_CONCAT(f.id) and you have all of the id's... note if you do that you need to add a GROUP BY at the end of your query to tell it how you want the results grouped

Thank you for this great source. I got many informtion which i had never looked in mysql. :)

Collectives™ on Stack Overflow

Mysql join query multiple values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related