A Question About 'from' In Mysql.

[ti8er]

Hi, 

Apologies in advance if the answer to this question is very obvious. I've tried googling but haven't found a satisfactory answer.

 

From page 220 of the Kindle version of your text book. Question 4: "For each user, find the number of messages they’ve posted, converting zeros to the string None"

 

The answer is:

 

SELECT u.username,

IF(COUNT(message_id) > 0, COUNT(message_id), 'None') AS Posts

FROM users AS u

LEFT JOIN messages AS m

USING (user_id)

GROUP BY (u.user_id);

 

Shouldn’t line three be “FROM messages AS m”?  (and then instead of "LEFT JOIN messages as m" you "LEFT JOIN users AS u") Since the “message_id” (used for the counting part on the previous line) is “FROM" the messages table and not from the users table? 

 

It's just a nagging question I had at the back of my mind cause it seems counter intuitive to the English meaning of the word "from". So I tried googling the MYSQL defintion of "FROM" to see if it means something different but couldnt find anything...

 

Many thanks for your assistance,

 

p.s. Your book has been very helpful. 

Edited August 19, 2017 by ti8er

[ti8er]

I totally understand the answer now...but unfortunately cant edit the post. I think the confusion was because the IF(COUNT...) line in the middle.

 

"username" is in the "users" table so "SELECT u.username.........FROM users AS u" makes sense. 

Hopefully that's the correct understanding. 

Thanks 

Edited August 29, 2017 by ti8er

[Larry]

Thanks for the nice words and sorry for the delayed reply! Arguably you could kind of name the tables in either order, because the JOIN of the two tables creates one big table and then the selection takes from that. Let me know if that still doesn't make sense!