CODEWITHSUNDEEP
mysqlsqldatabase
RNK
RNK

Reputation: 5792

mysql LEFT JOIN issue

There are two tables subscription and screen. There are more than one subscription for screens. I am using this query to get all the screens with number of subscriptions.

SELECT sc.id, sc.alias, sc.ad_capacity, COUNT(s.id) as ad_count FROM screen sc LEFT JOIN
subscription s ON sc.id = s.screen_id Group BY (s.screen_id);

But, I am getting only screens which are associated with subscriptions. I think LEFT JOIN should solve my problem. But, it's not.

Thanks.

Upvotes: 0

Views: 71

Answers (2)

actkatiemacias
actkatiemacias

Reputation: 1473

To elaborate more, MySQL, unlike other databases such as MSSQL, allows grouping without every item selected being in an aggregate function (sum, min, max etc) or part of the group by clause. This is not valid SQL and can cause a lot of headaches, like the one you have now.

In your example, if there are multiple rows in the subscription table with the same screen_id (likely since that's why you would have a foreign key, to represent one to many relationships) then it will take the FIRST row it finds from the subscription table with that id and get the remaining items selected (alias, capacity, etc) from that row rather than giving you all the values that exist. Which row it finds first is not always deterministic.

In short, include everything you are selecting in some aggregate function or part of the group by clause to avoid these headaches.

Upvotes: 0

eggyal
eggyal

Reputation: 125835

You need to group by sc.id, as s.screen_id will be the same (NULL) for all screens for which there are no subscriptions and thus all such screens are currently being grouped into a single resulting row.

Upvotes: 2

Related Questions