I have two tables.
There is several nid for each cid in the second table. What I would like to do is collapse the data in query in either temporary column or table as in the example.
biblio_contributor_data
+-----+----------+-----------+ | cid | lastname | firstname | +-----+----------+-----------+ | 1 | john | grand | | 2 | James | cook | | 3 | marco | palo | +-----+----------+-----------+
biblio_contributor
+-----+------+ | nid | cid | +-----+------+ | 4 | 1 | | 4 | 2 | | 4 | 3 | | 5 | 2 | +-----+------+
I would like a query result with the following if possible using SQL statement if possible:
+-----+------------------------------------+ | nid | temporary column | +-----+------------------------------------+ | 4 | john grand, James cook, marco palo | | 5 | James cook | +-----+------------------------------------+
Try this
SELECT b1.nid, group_concat(concat(firstname, " ", lastname))
from biblio_contributor b1, biblio_contributor_data b2 ON b1.cid=b2.cid
group by b1.nid
Use CONCAT to combine the lastname and firstname and use GROUP_CONCAT to combine the different rows.
Query
SELECT t1.nid,
GROUP_CONCAT(CONCAT(t2.lastname,' ',t2.firstname)) AS `temporary column`
FROM biblio_contributor t1
JOIN biblio_contributor_data t2
ON t1.cid=t2.cid
GROUP BY t1.nid;
Below works for me:
<?php
$stmt = 'SELECT t2.nid, group_concat(t1.lastname, t1.firstname) FROM `biblio_contributor_data` as t1 INNER JOIN biblio_contributor as t2 GROUP BY t2.nid';
$res = mysql_query($stmt);
while($rp = mysql_fecth_array($res)) {
// YOUR LOGIC GOES HERE
}
?>
Since until now no one threw in the manual page for GROUP_CONCAT, here it is:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
Please have a good read of it, esp. the part
The result is truncated to the maximum length that is given by the group_concat_max_len system variable