Lets assume that I have the following four tables in my database:
mag_id (primary key)
name (Nature, Time, etc)
cat_id (reference to Magazine_Category.cat_id)
cat_id (primary key)
category_name (Weekly, Monthly, etc)
user_id (primary key)
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.