Problem: How do I concatenate two MySQL tables using two different columns?
I have two MySQL Tables.
**DescriptionTable**. Fields: {filename, ID}.
**ResultsTable**. Fields: {query_id, media_id}. Both fields reference the ID field in the DescriptionTable.
A "match" links a query_id to a specified media_id, and an entry is added into ResultsTable.
I would like it so that I can do a SELECT query that retrieves the following:
[filename (query_id), filename (media_id)]
What I Have Tried:
SELECT a.filename
FROM DescriptionTable a, ResultsTable b
WHERE a.id = b.query_id
... but this only gives me the query_id's filename and not both of the filenames associated with query_id and media_id. How can I incorporate both in one SELECT command?
Join DescriptionTable to ResultsTable twice, once on query_id and once on media_id.
SELECT dq.filename as query_filename, dm.filename as media_filename
FROM ResultsTable r
INNER JOIN DescriptionTable dq ON r.query_id = dq.ID
INNER JOIN DescriptionTable dm on r.media_id = dm.ID
This should return rows with filenames for both query_id and media_id that correspond to the id pairs in the rows of ResultsTable.