Search code examples
mysqlsqljoininner-join

Perform an INNER JOIN with more than 2 tables in MySQL

I have tables payers, discounts, and items.

How can I return the results from all three tables at once using an INNER JOIN with all of the 3 tables?

How can I return the results from all three tables otherwise?

This doesn't work as expected:

SELECT *
FROM payers
INNER JOIN discounts AND items
ON payers.id = discounts.id AND ON payers.id = items.id;
Solution

  • You want two joins. The syntax is:

    SELECT *
FROM payers p
INNER JOIN discounts d ON d.id = p.id
INNER JOIN items     i ON i.id = p.id

    Side notes:

    • you did not show your actual schema, so this uses the join conditions described in your attempt; you might need to review that

    • table aliases make the query shorter to write and easier to read

    • SELECT * is generally not good practice; instead, I would recommend enumerating the columns you want in the SELECT clause, and properly aliasing conflicting columns names, if any (here, all three tables have a column called id, which would cause ambiguity in the resultset)