help with query

--0016e6d77d9bba14290499949d83
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I have 3 tables that I am trying to search across, and could use some help
on how to structure the query. I have a users table, a newsletter table,
and an articles table. The newsletter table has a user_id column, and the
articles table has a newsletter_id column. A user can have multiple
newsletters, and a newsletter can have multiple articles. What I would like
to do is find the list of users that have only newletters with no content.

My current query is as follows:
select users.id from users where users.id in (select newletters.user_id from
newletters left join articles on newletters.id = articles.newsletter_id
where articles.newsletter_id is null);

But I believe this is finding users that have any empty newletters, and not
users that have only empty newletters. How could I change this to return
only the users that have only empty newsletters?

Thanks,

Simon

--0016e6d77d9bba14290499949d83--

Re: help with query

On 1/11/11 9:31 AM, Simon Wilkinson wrote:
> select users.id from users where users.id in (select newletters.user_id from
> newletters left join articles on newletters.id = articles.newsletter_id
> where articles.newsletter_id is null);

I think this would do what you require:

SELECT
u.id AS user_id,
COUNT(DISTINCT n.id) AS num_newsletters,
COUNT(DISTINCT a.id) AS num_articles

FROM
users u
JOIN newsletters n ON n.user_id=u.id
LEFT JOIN articles a ON a.newsletter_id=n.id

GROUP BY
u.id

HAVING
num_newsletters > 0
AND num_articles = 0

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: help with query

--00032555e48ebfae520499a6c07c
Content-Type: text/plain; charset=ISO-8859-1

Thank you, that did the trick.

Simon

On 11 January 2011 12:09, Steve Meyers wrote:

> On 1/11/11 9:31 AM, Simon Wilkinson wrote:
>
>> select users.id from users where users.id in (select newletters.user_id
>> from
>> newletters left join articles on newletters.id = articles.newsletter_id
>> where articles.newsletter_id is null);
>>
>
> I think this would do what you require:
>
> SELECT
> u.id AS user_id,
> COUNT(DISTINCT n.id) AS num_newsletters,
> COUNT(DISTINCT a.id) AS num_articles
>
> FROM
> users u
> JOIN newsletters n ON n.user_id=u.id
> LEFT JOIN articles a ON a.newsletter_id=n.id
>
> GROUP BY
> u.id
>
> HAVING
> num_newsletters > 0
> AND num_articles = 0
>

--00032555e48ebfae520499a6c07c--