带有两个WHERE子句的MySQL SELECT语句

Question

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?

Answer 1

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.