Cannot figure out how to fix query. Repeats results!

I have a user login system that allows for files to be uploaded into specific folders. The problem I am experiencing is that when you go to view the files that have been uploaded, if a file was categorized into two folders, two separate entries for the same file will appear.

files
+------+---------+-------+
| file_id | name __| author |
+------+---------+-------+
| 28 __| logo.png | 1 ____|
+------+---------+-------+

files_folders
+------+----------+
| file_id | folder_id |
+------+----------+
| 28 ___| 3 ______|
| 28 ___| 1 ______|
+------+----------+

folders
+---------+------------+-----------------+
| folder_id | folder_name | parent_folder_id |
+---------+------------+-----------------+
| 1 ______| ABC ______| 0 _____________|
| 3 ______| XYZ ______| 0 _____________|
+---------+------------+-----------------+

I need to select all the files and display the folder it's in, but I cannot have duplicate entries if the file is categorized into two or more folders.

My query so far is...

SELECT * FROM files LEFT JOIN files_folders ON files.file_id = files_folders.file_id LEFT JOIN users on files.author = 1 LEFT JOIN folders on files_folders.folder_id = folders.folder_id ORDER BY files.file_id DESC

I tried using GROUP BY but that didn't work.

I would appreciate any and all help. This is giving me a headache!

hint:

SELECT group_concat(distinct folder_id)
FROM files
LEFT JOIN files_folders ON files.file_id = files_folders.file_id
LEFT JOIN users on files.author = 1
LEFT JOIN folders on files_folders.folder_id = folders.folder_id
group by file_id

The group_concat was a great hint, but your formula didn't work. Ugh!

GROUP_CONCAT makes no sense to me. You would use that if you *DID* want to show *ALL* folders a file is in, say in a comma-delimited list.

If you only want to show *ONE OF SEVERAL* folders that a file is in, then *WHICH* folder do you want to show?

In the example you gave, would you want to see
logo.png is in folder ABC
or

logo.png is in folder XYX
or does it matter? Is it okay to pick either one?

When you want a computer language (of any kind, but certainly including SQL) to pick only one of several choices, it is up to you to tell it *which* choice to make.

In this case an easy solution would be to say pick the first folder by name, alphabetically (or last, alphabetically).

So then it is dirt simple:

SELECT files.name, users.name AS authorname, MIN(folders.folder_name) AS folder
FROM files
LEFT JOIN files_folder ON files.file_id = files_folder.file_id
LEFT JOIN folders ON files_folder.folder_id = folders.folder_id
LEFT JOIN users ON users.user_id = files.author
GROUP BY files.name, users.name

The killer in any query attempts of this nature is the use of SELECT *
Try real hard to stop using SELECT * and start choosing only the fields you really need to choose.

GROUP BY only works correctly when you specify *ALL* the NON-AGGREGATE fields (that is, all the fields other than those in aggregate functions, which are MAX(), MIN(), SUM(), COUNT(), etc.).

I would also comment that you may/probably don't need LEFT JOINs there, at least not for all the joins. *ONLY* if a file might *NOT* have an author (that is, if the files.author field might be NULL) should you LEFT JOIN to users. *ONLY* if a file might not be found id *ANY* folder should use the other two LEFT JOINs. Convert the LEFTs to INNER if appropriate.

Thank you so much!