CODEWITHSUNDEEP
mysqlsql
Areg
Areg

Reputation: 1485

PHP MySQL comparing two tables and getting values

I have two tables, one is called contacts and the other one is called numbers. One stores contact information and looks like this 

contacts
-------------------------------------------------------
| id | fname | lname | email | address | uid | uniqid |
-------------------------------------------------------

My second table which stores phone numbers that belong to specific contact look like this 

numbers
---------------------
| id | number | cid |
---------------------

The cid is the same as the uniqid on contact table, how can i get the contact row with its numbers which is on the second table through mysql?

Update Correction to the correct answer

SELECT id ,fname ,lname ,email ,address , uid, uniqid,number
FROM contacts a
inner join (SELECT cid, GROUP_CONCAT(DISTINCT number SEPARATOR ',') number FROM numbers) b ON b.cid=a.uniqid

It was missing DISTINCT

Upvotes: 0

Views: 127

Answers (6)

Yogesh Kolhe
Yogesh Kolhe

Reputation: 11

Here we are using the concept of foreign key . Here cid is foreign key of contact table on number table. we have to match primary key of contact table with the foreign key of number table. if both are match then it's show the result.

Select a.id, a.fname, a.lname, a.email, a.address, 
a.uid, a.uniqid,b.number from contact a, number b where a.id=b.id;

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You can use GROUP_CONCAT to get multiple numbers to one row and then when you imply the join you won't get duplicates.

select `id` ,`fname` ,`lname` ,`email` ,`address` , `uid`, `uniqid`,`number`
from `contacts` a
inner join (Select `cid`, GROUP_CONCAT(`number` seperator ',') `number` from `numbers`) b on b.cid=a.uniqid

Upvotes: 1

Shadiqur
Shadiqur

Reputation: 492

using join is the right choice here:

  SELECT con.*,num.* from contacts as con inner join numbers as num on con.uniqid = num.cid

Upvotes: 1

M.Hemant
M.Hemant

Reputation: 2355

Just use inner join with n.cid = c.uniqid

select c.id,c.fname,c.lname,c.email,c.address,c.uid,c.uniqid,n.number 
from contacts c 
inner join numbers n 
on n.cid = c.uniqid

Upvotes: 1

lifeisbeautiful
lifeisbeautiful

Reputation: 846

You can map the two id's make sure you have this as table index, for faster retrieval of data.

SELECT id ,fname ,lname ,email ,address , uid, uniqid, number from contacts a, number b WHERE a.uniqid = b.cid;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use join

select id ,fname ,lname ,email ,address , uid, uniqid,number
from contacts a
inner join numbers b on b.cid=a.uniqid

Upvotes: 2

Related Questions