CODEWITHSUNDEEP
mysql
GuruMeditation
GuruMeditation

Reputation: 1882

MySQL - Merge first query results into a column of the second query

I have two MySQL tables and I would like to know if there is any way to merge the results of the first query so that they appear as a column on the second query. Here is an example:

Table 1

FolderTable

ID  NAME    PATH
1    c: 
2    tmp     1
3     test    1,2

SELECT name FROM FolderTable WHERE id IN (1, 2, 3)
Result:
c:
tmp
test

Table 2

ImageTable

ID  NAME        FOLDER_ID
1    hi.jpg       3
2    blah.jpg     3
3    overwlow.jpg 3

SELECT name FROM ImageTable WHERE id = 2
Result:
NAME
blah.jpg

WHAT I WANT:

Result:
NAME        PATH
blah.jpg    c:\tmp\test

How do I write a querty that merges the output of the first query into a PATH column of the second query separated by "\"? Is this possible?

Upvotes: 0

Views: 891

Answers (2)

manji
manji

Reputation: 47978

SELECT name,
       SELECT GROUP_CONCAT(name ORDER BY ID SEPARATOR '\')
         FROM FolderTable 
        WHERE id BETWEEN (SELECT MAX(ID)
                            FROM FolderTable
                           WHERE ID < ImageTable.FOLDER_ID
                             AND PATH IS NULL) 
                     AND ImageTable.FOLDER_ID
 FROM ImageTable WHERE id = 2

Upvotes: 1

Fergal Moran
Fergal Moran

Reputation: 4634

Look into INNER JOIN

SELECT 
    FolderTable.Name AS FolderName, ImageTable.Name AS ImageName 
FROM 
    FolderTable 
INNER JOIN 
    ImageTable ON ImageTable.FolderId = FolderTable.ID

You can concatenate the fields together with "\" if you wish

Upvotes: 0

Related Questions