How to include all Magazines

[ryy705]

Hello,

Lets assume that I have the following four tables in my database:

Magazine:
mag_id (primary key)
name (Nature, Time, etc)
cat_id (reference to Magazine_Category.cat_id)

Magazine_Category:
cat_id (primary key)
category_name (Weekly, Monthly, etc)

User
user_id (primary key)
user_name

Subscription
user_id (reference to User.user_id)
mag_id (reference to Magazine.mag_id)

Now I wish to display each magazine's id, name, category, and how many users subscribe to it.

If I were to try the following:

SELECT Magazine.mag_id, Magazine.mag_name, Category.name, COUNT(Subscription.user_id) FROM Magazine, Magazine_Catergory, Subscription WHERE Magazine.cat_id= Magazine_Category.cat_id AND Magazine.mag_id=Subscription.mag_id GROUP BY Magazine.mag_name ORDER BY Magazine.mag_name

It would give me all magazines that have at least one subscriber. But how can I also include magazines that do not have a subscriber yet? Please help.
Kindly ask if something is not clear.

[mwasif]

This should work for you

SELECT mag.mag_id, mag.name, mag_cat.category_name, COUNT(*) AS subscribers 
FROM Magazine mag 
LEFT JOIN Magazine_Category mag_cat ON mag.cat_id = mag_cat.cat_id 
LEFT JOIN Subscription sub ON mag.mag_id = sub.mag_id
GROUP BY mag.mag_id