[SOLVED] 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.

[xtopolis]

You will want to use a LEFT join.  Perhaps something like this to get you started, (untested):

SELECT m.mag_id, m.name, COUNT(s.user_id)
FROM Magazine m
LEFT JOIN Subscription s USING(mag_id)

 

Try that and see what it comes up with.

[ryy705]

Many thanks