One to many link table causing duplicate rows returned

Question

Everything I've seen so far is about removing duplicate entries in a database automatically. I want to stress at the beginning of this that there is no duplicate data in the database. I'll also start with the fact that I'm very much still learning about RDBMS design, normalization, relationships, and, most of all, SQL!

I have a table of clients, with a clientid (PK) and a client_name. I have a table of roles, with a roleid (PK) and a role_name. Any client can have multiple roles associated with it. So I created a client_role_link table, with clientid and roleid as the two fields. Then I run this:

SELECT client.client_name, role.role_name FROM client 
  LEFT JOIN client_role_link ON client_role_link.clientid=client.clientid 
  LEFT JOIN role ON client_role_link.roleid=role.roleid 
  WHERE (role.roleid='1' OR role.roleid='2')

So let's say I have a client that has two roles associated with it (roles '1' and '2'). This query returns two rows, one for each role. When I get these results back I'm using a while loop to cycle through the results and output them into a <select> list. It's then causing two <option>'s with the same client listed.

I understand why my query is returning two rows, it makes sense. So here comes the two fold question:

1. Is there a better database/table design that I should be using, or a more optimized query?
2. Or is this something I should handle in the PHP? If so, is there a more elegant solution that adding all the results into an array, then looping back through the array and removing duplicates?

Thoughts?

Answer 1

If you want to show both roles, then your query is OK.

MySQL does not support array datatypes, so you should fill an associative array on the PHP side using the resultset with the duplicate client names.

If you just need to show clients having either of the roles, use this query:

SELECT  c.*
FROM    client c
WHERE   c.clientid IN
        (
        SELECT  roleid
        FROM    client_role_link crl
        WHERE   crl.roleid IN (1, 2)
        )

This will return one record per client but won't show any roles.

The third way would implode the role names on the server side:

SELECT  c.*, GROUP_CONCAT(role_name SEPARATOR ';') AS roles
FROM    client c
LEFT JOIN
        client_role_link crl
ON      crl.clientid = c.clientid
        AND crl.roleid IN (1, 2)
LEFT JOIN
        role r
ON      r.roleid = crl.roleid
GROUP BY
        c.id

and explode them on PHP side, but make sure role names won't mix with the separator.

Answer 2

You could use the aggregate SQL function group_concat() with a GROUP BY clause on the client_name field. This will return a result set with a single row containing a comma-delimited string with the roles.

In PHP you could explode() that string and iterate over the resulting array instead of fetching row by row.

Answer 3

You could use mysql_fetch_assoc() to get them back in array form. Then you could have something like code untested, but may work:

$sql = "SELECT client.id, client.client_name, role.role_name FROM client LEFT JOIN client_role_link ON client_role_link.clientid=client.clientid LEFT JOIN role ON client_role_link.roleid=role.roleid WHERE (role.roleid='1' OR role.roleid='2')";
$result = mysql_query($sql);
$res = array();
while ($row = mysql_fetch_assoc($result)) {
    $res[$row['id']]['roles'][] = $row['role_name'];
    $res[$row['id']]['client_name'] = $row['client_name']; //you'll be overwriting each iteration probably a better way
}