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.
SELECT f.familynumber, GROUP_CONCAT(p.phonenumbers) AS numbers
FROM familytable f, phonetable p
WHERE f.familynumber = p.familynumber
GROUP BY familynumber
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.
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
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