Simple SQL query, or maybe not

[eluser]Boris Strahija[/eluser]
So I have 3 tables:
newsletter_subscribers
newsletter_categories
newsletter_categories_subscribers

The 3rd one is a relation table, and it contains the subscriber_id and the category_id.
Is there a way to get all subscribers, from subscribers and also all the names of the subscribed categories. The problem is that one subscriber can have multiple entries in the relation table.
I'm doing a simple join now, and I'm getting an entry for every subscription, so if a subscriber is subscribed to lets say 3 categories, I'll get 3 rows with the same email address.
Is it even possible to do this with one query?
Or should I reorganize the object list with duplicate entries?

Thanx!

[eluser]WanWizard[/eluser]
Looks to me like a standard join in an N:M relationship.

Code:

SELECT * FROM newsletter_subscribers JOIN newsletters_categories_subscribers USING subscriber_id JOIN newsletters_categories USING category_id

[eluser]Boris Strahija[/eluser]
Thanx for the fast reply, but it's not working. Maybe it's the lack of sleep, but I'm not seeing the error

My DB structure is like this:
newsletter_subscribers: id, user_id, email, first_name...
newsletter_categories: id, title, body, status...
newsletter_categories_subscribers: id, category_id, subscriber_id

Oh, and this is my current query:

Code:

SELECT `bd_newsletter_subscribers`.*, `bd_newsletter_categories`.`title` AS category_title
FROM (`bd_newsletter_subscribers`)
JOIN `bd_newsletter_categories_subscribers` ON `bd_newsletter_categories_subscribers`.`subscriber_id` = `bd_newsletter_subscribers`.`id`
JOIN `bd_newsletter_categories` ON `bd_newsletter_categories`.`id` = `bd_newsletter_categories_subscribers`.`category_id`
ORDER BY `category_title`, `id`, `id` ASC

[eluser]WanWizard[/eluser]
What is the expected output, and what is the output you get?

Because besides the fact that you sort twice on the 'id' field I don't see any errors in this query.

How do you handle NULL values? Do you have subscribers without categories, or the other way around? And do you expect to see those in the result?

[eluser]Boris Strahija[/eluser]
The query is generated with active record, and yes I've put order_by('id') by mistake twice.

Now I'm getting 1 row for every subscription, meaning that if 1 user is subscribed to 3 categories I get 3 rows. What I would like is get 1 row for a user, and then maybe something like this:

Code:

$subscriber->categories = "1,2,3";

or

Code:

$subscriber->categories = array(1,2,3);

My question was if something like this is even possible with one query?
I know I can do a foreach() and reorganize the results, but I was thinking maybe it's possible inside the query.

[eluser]vitoco[/eluser]
GROUP BY maybe it's what you need , you can CONCAT the fields

Code:

$sql = "
SELECT
    ns.* ,
    CONCAT( category_id , ',' ) AS categories
FROM
    newsletter_subscribers AS ns
INNER JOIN
    newsletters_categories_subscribers AS ncs
USING
    ( subscriber_id )
INNER JOIN
    newsletters_categories AS nc
USING
    ( category_id )
GROUP BY
    subscriber_id

then you can explode( ',' , $row['categories'] ) and get an array of the categories associated with the suscriber

Saludos