[RESOLVED] Multiple rows in single column

computerzworld

Hello,
I am having one master table which is having one relational table. Relational table should can be having zero,one or more than one values inside it & I want to display all the fields from master along with one field of relational table using mysql query.

Here is my table structure.

master_contacts

contactId      contactName    contactCompany
1                    a            c1


relative_groups
contactId      groupId              

1                    1                          

1                    2                          

2                    3

Now this relative table can be having any number of results pertaining to particular contactId. I want to display contactName,contactCompany & groupName as result. So, if particular contact is assigned to more than one groups then the query result should be something like this...

contactName     contactCompany     groupName
a                     c1                     1, 2
b                     c2                     2

How can I display such kind of results using one select query? Please help me. Thanks in advance..

djjjozsi

Here is an example: http://guelphdad.wefixtech.co.uk/sqlhelp/catsubcat.shtml

select the content from the three table, and write an IF condition inside the while function,
to check if the previous row has the same value (contactId) , if not print the next row.

Your query would be more difficult then a simple triple JOIN if you concated the category names as you think.

Relational table should can be having zero

If one or more company hasn't got a category then you didn't need to insert in the relative_groups table.

And then the query looks like this:

SELECT
  `master_contacts`.`contactId` , `master_contacts`.`contactName` , `master_contacts`.`contactCompany` , `groups`.`groupId` , `groups`.`groupName`
FROM
  ((`master_contacts` LEFT JOIN `relative_groups` ON ((`master_contacts`.`contactId` = `relative_groups`.`contactId`))) LEFT JOIN `groups` ON ((`relative_groups`.`groupId` = `groups`.`groupId`)))
GROUP BY
  `master_contacts`.`contactId`, `master_contacts`.`contactName`, `master_contacts`.`contactCompany`, `groups`.`groupName`

Hello, jjozsi.

computerzworld

its solved... 🙂 just use group_concat & its done....