We have a real brain teaser of MySQL Select to create and we cannot seem to get it, so I was wondering if you all can and whether it is even possible. So we have 2 Tables:
1- Folders Tables (AS ft) contains the name of folders and user_id of user who created them
2- Notes Tables (AS nt) contains notes taken by members, one of the fields of this Table contains the Folder name that this note belongs to
So Folders can exist on ft which have no matching notes for them on nt as a user can create Folders and not post any notes to these folders until later.
So the challenge is how to have a Select that shows all folders of ft belonging to user_id 99 and then how many corresponding notes that folder has as existing in nt? Keeping in mind that of course folders from ft can have Zero corresponding notes in nt.
I tried this, but not working:
SELECT mnf.folder, COUNT(mn.id) AS notes FROM my_notes_folders AS mnf LEFT JOIN my_notes AS mn ON mnf.user_id = mn.user_id WHERE mn.user_id = 99 ORDER BY mnf.folder ASC;
No, it is not that simple.
Here it is with Group By:
SELECT mnf.folder, COUNT(mn.id) AS notes FROM my_notes_folders AS mnf LEFT JOIN my_notes AS mn ON mnf.user_id = mn.user_id WHERE mn.user_id = 121 GROUP BY mnf.folder ORDER BY mnf.folder ASC;
All this does is list all the Folders but the Count in front of each Folder name is the Total of all notes by this user and not the total notes in that folder.
you had a WHERE condition on the right table in a left outer join, making it behave like an inner join
since the user_ids have to be equal, put the condition on the left table
try it this way –
SELECT mnf.folder
, COUNT(mn.id) AS notes
FROM my_notes_folders AS mnf
LEFT
JOIN my_notes AS mn
ON mn.user_id = mnf.user_id
WHERE mnf.user_id = 121
GROUP
BY mnf.folder
ORDER
BY mnf.folder ASC
but you said “folders of ft belonging to user_id 99 and then how many corresponding notes that folder has as existing in nt?”
and the key here is “corresponding” – and you yourself posted the query with LEFT JOIN my_notes AS mn ON mnf.user_id = mn.user_id thus linking them via user_id
but then you said this is wrong because “the Count in front of each Folder name is the Total of all notes by this user and not the total notes in that folder”
you’re going to have to explain the relationship between the tables a little more clearly in order to assist in getting a solution you’re happy with
So the NT Table contains all Notes taken by Users.
The FT Table is the name of Folders created by users.
So all Notes on NT have a Folder name that they belong to.
But all Folders in FT may have NO Notes taken with them as the Folder name and there could be X number of Notes taken with the same Folder name.
Keeping in mind that a User can:
1- Create a Folder and take No Notes with that Folder name until later
2- Many Notes taken by a User can have the same Folder assignment
So the Key point in mind is that Notes in NT Table and Folders in FT Table share the same user_id Foreign Key and nothing else.
And it is also the fact that your MySQL suggestion produces False result. If you want I can send you a screen shot of the result and you see for yourself that the result is false as it is showing same number of Notes for ALL folders.
As I said at the start, this is a very complex MySQL question, a “real brain teaser”.
far far more useful would be a test case, containing CREATE TABLE statements as well as enough INSERT statements of your actual data to demonstrate what’s going on
It is not much of a teaser, its a plain and simple query.
Look over the use of the foreign keys for the notes, you join it by the user. This is why all folders show the same count.
If you join the notes with the folder key instead you should be good (assuming a folder can only contain notes from one user, if not, in addition to the folder key, join on the user as now).
Actually after my chat yesterday, I did try this. And we are now getting a list with All folders and correct count of Notes in each Folder, BUT the Folder will no Notes is not being listed with 0 Notes in front of it. Here:
SELECT mnf.folder, COUNT(mn.id) AS notes
FROM my_notes_folders AS mnf
LEFT JOIN my_notes AS mn
ON mnf.folder = mn.folder
WHERE mn.user_id = 99
GROUP BY mn.folder
ORDER BY mnf.folder ASC;
Sorry, but I do not know what you are talking about!
So, here is the Code, change it to what you think will work:
SELECT mnf.folder, COUNT(mn.id) AS notes
FROM my_notes_folders AS mnf
LEFT JOIN my_notes AS mn
ON mnf.folder = mn.folder
WHERE mn.user_id = 99
GROUP BY mn.folder
ORDER BY mnf.folder ASC;
SELECT mnf.folder
, COUNT(mn.id) AS notes
FROM my_notes_folders AS mnf
LEFT
JOIN my_notes AS mn
ON mn.folder = mnf.folder
WHERE mnf.user_id = 99
GROUP
BY mnf.folder
ORDER
BY mnf.folder ASC;
