Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post

    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!

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    hint:
    Code:
    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
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Regular Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    The group_concat was a great hint, but your formula didn't work. Ugh!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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
    Code:
    logo.png is in folder ABC
    or
    Code:
    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:
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thank you so much!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •