join two tables in mysql and get records

Question

I have two tables "contacts" and "users". Users table storing data with "," separated. Need to distinct data in "Contacts" column from "Contacts" table. And need to join with "Users" table, and get the records.

Contacts Table
--------------------------
id |    user_Id      | contats
--------------------------
1  |   2147483647    | 90123456789,90123456789,90123456789,90123456789
2  |   2147483647    | 90123456789,90123456789,90123456789,90123456789
3  |   919444894154  | 90123456789,90123456789,90123456789,90123456789

Users Table
-----------------------------
id | username | email                  | phone
-----------------------------
1  | bhavan   | bhavanram93@gmail.com  | 90123456789
2  | bhavan   | bhavanram93@gmail.com  | 90123456789
3  | prince   | prince@gmail.com       | 1234567980
4  | bhavan   | bhavanram93@gmail.com  | 90123456789
5  | hello    | hello@gmail.com        | 1234567890
6  | bhavan   | bhavanram93@gmail.com  | 90123456789

Check this explained well here - https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Cruzer, Nov 29 '17 at 13:33
Post the tables and data as text [READ THIS](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — Juan Carlos Oropeza, Nov 29 '17 at 13:47

score 1 · Answer 1 · answered Nov 29 '17 at 13:38

1

Your table Contacts shouldn't be constructed this way.

Since you want 1 Users table containing all the data about a user, and 1 Contacts table containing links between different users, you'd rather do this kind of table structure :

Contacts table

id | user_id | contact_id
-------------------------
1  | 1       | 2
2  | 1       | 3
3  | 2       | 3

That'll allow you to do something like :

SELECT *
FROM Users
JOIN Contacts ON (Users.id = Contacts.contact_id)
WHERE Contacts.user_id = 1

Which will return all the data of the contacts of the user 1.

Your current structure is a huge ongoing mess, it's the opposite of being flexible.

answered Nov 29 '17 at 13:38

Steve Chamaillard

2,289
17
32

Thanks for the reply, We need to remove " ' " (comma) from "contacts" column from "contacts" table right? That i want to. – kumar Nov 29 '17 at 13:45
Yes absolutely. For example in your example in your ```contacts``` column there is ```"919444894154,91944654546"```, when you should have 2 lines instead following the same pattern than in my example. – Steve Chamaillard Nov 29 '17 at 13:55
https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows provides an example of how to take a comma separated list and make rows out of it. – xQbert Nov 29 '17 at 14:10

score 1 · Answer 2 · answered Nov 29 '17 at 14:11

You should restructure your db to a normalized format as Steve suggest.

But if you cant:

SELECT *
FROM Users
JOIN Contacts 
  ON CONCAT(',', Contacts.contacts, ',') like
     CONCAT('%,', Users.phone, ',%')
WHERE Contacts.user_id = 1

the idea is you convert your contacts to

,    <numbers>    ,
,90123456789,90123456789,90123456789,90123456789,

and try to match with

 %,90123456789,%

Note this approach cant use any index so will have bad performance with many rows. if you are in the order of 1k-10k rows may be ok. More than that you need consider restructure your db.

join two tables in mysql and get records

2 Answers2