sql join within join?

Question

I need your help building a SQL statement I can't wrap my head around.

In a database, I have four tables - files, folders, folders_files and links.

I have many files. One of them is called "myFile.txt".

I have many folders. "myFile.txt" is in some of them. The first folder it appears in is called "firstFolder".

I have many links to many folders. The first link to "firstFolder" is called "firstLink".

The data structure for the example would be:

// files
Id: 10
Name: "myFile.txt"

// folders
Id: 20
Name: "firstFolder"

// folder_files (join table)
Id: 30
Folder_Id: 20 (meaning "firstFolder")
File_Id: 1 (meaning "myFile.txt")

// links
Id: 40
Name: "firstLink"
Folder_Id: 20 (meaning "firstFolder")

FIRST QUESTION: How do I get the record for "myFile.txt" AND the Name and Id of "firstLink" (the first link), querying on file Id = 10, based on the lowest Id of the folder and the link?

SECOND QUESTION: How do I get the record for "myFile.txt" AND the Name and Id of "firstLink" (the first link), querying on all files, based on the lowest Id of the folder and the link?

put another way - how do I get the first link to the first folder containing "myFile.txt"?

Resulting in a record that looks like:

Id: 10
Name: "myFile.txt"
LinkId: 40
LinkName: "firstLink"

Thanks!

Answer 1

This selects all links for file id 10:

select links.id, links.name
 from files
 left join folder_files on files.id = folder_files.file_id
 left join folders on folder_files.folder_id = folders.id
 left join links on links.folder_id = folders.id
 where files.id=10;

Change the where clause, add limit or whatever for other things you want. It should be simple to modify this.

Answer 2

You should try to think about how you want your result set to look. SQL is designed to describe result sets. If you can write out a hypothetical result set, you might have an easier time writing SQL that will render that result set.

I had a hard time understanding what you are looking for, but I'm sure it's a fairly straight forward problem. I would be able to help you easier if you could describe you results more clearly, although you might not need my help anymore!

For example (going with you original schema) Q1 & Q2:

files.Id, files.Name, links.Id, links.Name (4 columns)

Q1:

SELECT
  files.Id, files.Name, links.Id, links.Name
FROM
  files, links
INNER JOIN
  folder_files
  ON files.Id = folder_files.File_Id
INNER JOIN
  links
  ON links.Id = folder_files.Folder_Id
WHERE
  files.Id = 10
ORDER BY
  folder_files.File_Id ASC, links.Id ASC
LIMIT 1;

(JOIN with folders table not necessary)

Q2:

Change both ASC to DESC

Answer 3

I would try this:

select f.*
        , l.Id as LinkId
        , l.Name as LinkName, 
    from Link l
        inner join Folder_Files ff on ff.Folder_Id = l.Folder_Id
        inner join Files f on f.Id = ff.File_Id
    where f.Id = 10

Resulting to:

 Id | Name       | LinkId | LinkName
 10 | myFile.txt | 40     | firstLink

Is this what you want?

Answer 4

Taking into account:

• more folders per file
• more links per folder
• taking the lowest id folder for link, and lowest id link for folder

With help of: http://stackoverflow.com/questions/3097321/mysql-group-by-id-get-highest-priority-per-each-id/3097336#3097336

The answer for ALL files in the files table ( go for JohnB's solution for a single file, it would be faster):

SELECT file_id, file_name, link_id, link_name FROM (
SELECT file_id, file_name, link_id, link_name,
    @r := CASE WHEN @prev_file_id = file_id
                    THEN @rn + 1
                    ELSE 1
               END AS r,
    @prev_file_id := file_id
FROM (
    SELECT
        f.id as file_id, f.name as file_name, l.id as link_id, l.name as link_name
    FROM files f
    JOIN folder_files ff
    ON ff.file_id = f.id
    JOIN links l
    ON l.folder_id = ff.folder_id
    ORDER BY ff.folder_id, l.id -- first folder first, first link to first folder second
) derived1,
(SELECT @prev_file_id := NULL,@r:=0) vars
) derived2
WHERE r = 1;