CODEWITHSUNDEEP
mysqlphpmyadmin
kumar
kumar

Reputation: 147

join two tables in mysql and get records

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   | [email protected]  | 90123456789
2  | bhavan   | [email protected]  | 90123456789
3  | prince   | [email protected]       | 1234567980
4  | bhavan   | [email protected]  | 90123456789
5  | hello    | [email protected]        | 1234567890
6  | bhavan   | [email protected]  | 90123456789

Upvotes: 0

Views: 809

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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.

Upvotes: 1

Steve Chamaillard
Steve Chamaillard

Reputation: 2339

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.

Upvotes: 1

Related Questions