Display output by one sql from two tables

Question

family table

familynumber fistname lastname
10001        A        B
10002        C        D
10003        E        F
10004        G        H

phone table

familynumber phonenumber description
10001        111         mobile
10001        222         home
10001        333         text
10002        444         text
10003        555         work
10003        666         mobile

I need output as

familynumber phonenumbers
10001        111, 222, 333
10002        444
10003        555, 666

I know it can be done by folowing code

$families = mysql_query("select * from family")
while($family = mysql_fetch_assoc($families))
{
    extract($family);
    echo "<td>$familynumber</td>";
    echo "<td>";
    $phones = mysql_query("select * from phone where familynumber = '$familynumber'");
    while($phone = mysql_fetch_assoc($phones))
    {
        extract($phone);
        echo $phonenumber . ", ";
    }
    echo "</td>";

}

But I need this output in one sql query.

look on group_concat mahmudahsan.wordpress.com/2008/08/27/… — Haim Evgi
– Haim Evgi, Commented Jul 21, 2011 at 6:45

Haim Evgi · Accepted Answer · 2011-07-21 07:03:58Z

2

using GROUP_CONCAT function

SELECT p.familynumber,
GROUP_CONCAT(DISTINCT p.phonenumber ORDER BY p.phonenumber SEPARATOR ",")
AS phonenumbers 
FROM phone p LEFT JOIN family f ON p.familynumber = f.familynumber
GROUP BY familynumber

edited Jul 21, 2011 at 7:03

answered Jul 21, 2011 at 6:48

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sureshkumar Over a year ago

I also need family table fields

Jacob · Accepted Answer · 2011-07-21 06:47:02Z

0

SELECT f.familynumber, GROUP_CONCAT(p.phonenumbers) AS numbers
FROM familytable f, phonetable p
WHERE f.familynumber = p.familynumber
GROUP BY familynumber

GROUP_CONCAT

answered Jul 21, 2011 at 6:47

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

Comments

Jess · Accepted Answer · 2011-07-21 06:47:29Z

0

Well, you can always use a left join. That is probably what you're looking for something like: SELECT * FROM family LEFT JOIN phone ON family.familynumber=phone.familynumber

Note: This will return an entire row for each phone number type (so if a family has 3 phone numbers, it will return the families info 3 times).

More on left joins: http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

Note, there are also Right joins, and Inner joins. You should learn, and memorize these, they are dispensable. When you get better at them, look up optimizing joins. There are some great articles, that can save you precious time.

answered Jul 21, 2011 at 6:47

Jess

8,7306 gold badges51 silver badges68 bronze badges

Comments

Rukmi Patel · Accepted Answer · 2011-07-21 06:49:09Z

0

SELECT familynumber, phonenumbers FROM family AS fml INNER JOIN phone AS ph ON fml.familynumber = ph.familynumber GROUP BY ph.familynumber

I am not sure but this may help you or put you in better position

answered Jul 21, 2011 at 6:49

Rukmi Patel

2,5619 gold badges29 silver badges42 bronze badges

Collectives™ on Stack Overflow

Display output by one sql from two tables

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related