Left join, or?

[bayswater]

Hi,

I have a list of articles, for which it is supposed to be noted if an article is relevant for only a certain user group. I have three tables:

1. articles:
   article_id (autonum) | article_name

2. userroles:
   role_id (autonum) | role_name

3. context:
   context_id (autonum) | context_article_id | context_role_id

As an example the article "PHP for beginners" (with article_id 3) is relevant for the user group "Students" (role_id 4) and "Teachers" (role_id 5) but NOT relevant for "Secretaries" (role_id 7)

In the database:

1. articles:
   3 | PHP for beginners

2. userroles:
   4 | Students
   5 | Teachers
   7 | Secretaries

3. context: (here is context_id set to 12 og 13 as an example)
   12 | 3 | 4
   13 | 3 | 5

My problem is that when editing a article - for example to change the relevant user groups I can't get the SQL to work. It should list ALL the exisisting user roles to chose from, and mark the ones (in a check box) that are selected for the specific/chosen article.

My attempt:

$sql_roles = mysql_query("SELECT DISTINCT r.role_id, r.role_name, c.context_id, c.context_article_id, c.context_role_id, a.article_id

FROM (userroles r LEFT JOIN context c ON c.context_role_id = r.role_id)
LEFT JOIN articles a ON a.article_id = c.context_sc_id

ORDER BY r.role_name asc");

But this lists some roles that are used by another article more than once (role_id in ()):

PICKED (5) Teachers
(5) Teachers
(7) Secretaries
(8) Super Users
(4) Students
PICKED (4) Students

How should my SQL look to accomplish this?

[planetphp]

you probably need to introduce a GROUP BY clause into the end of your statement.

[johanafm]

You seem to be missing a where clause with context_article_id = @current_article_id

[bayswater]

planetphp;10994762 wrote:

you probably need to introduce a GROUP BY clause into the end of your statement.

Could you elaborate a bit on that? Putting GROUP BY r.role_id reduces the list to show only one instance of the role (which is good) - but for some reason it leaves out one being picked:

PICKED (5) Teachers
(7) Secretaries
(8) Super Users
(4) Students

[bayswater]

johanafm;10994764 wrote:

You seem to be missing a where clause with context_article_id = @current_article_id

But wouldn't that only show me the roles that are attached to the current article? I want ALL roles shown, and those attached to the current article should be checked (checkbox)

[johanafm]

bayswater;10994766 wrote:

But wouldn't that only show me the roles that are attached to the current article?

Would it?

bayswater;10994766 wrote:

I want ALL roles shown, and those attached to the current article should be checked (checkbox)

And the where condition would give you?

Also, any way at all you can think of to answer these two questions?