Two tables Select Inner Join with order by and where and limit clauses

Question

So i have this two tables:

socc_matches:

`mc_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`mc_analystid` int NOT NULL,
`mc_teamid` int NOT NULL, 
`mc_date` date NOT NULL,
`mc_hometeam` varchar(60) NOT NULL,
`mc_awayteam` varchar(60) NOT NULL,
`mc_shots` tinyint(40)

socc_shots:

`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`idmatch` int NOT NULL,
`idplayer` int,
`playername` varchar(60)

I want to get all the rows from socc_shots where idmatch is equal to the LAST 3 mc_id from table socc_matches order by date

Note: idmatch is Foreign Key of mc_id

I've tried this query:

$conn = connection ();
$stmt = $conn->prepare('SELECT * FROM socc_shots INNER JOIN socc_matches ON socc_matches.mc_id = socc_shots.idmatch WHERE socc_matches.mc_id = socc_shots.idmatch  ORDER BY socc_matches.mc_date desc LIMIT 3');

if (!$stmt->execute()) {
    print_r($stmt->errorInfo());
    }

   $r = $stmt->fetchAll();

But is not retrieving the data that i want (the query is wrong)

What am i missing?

Thanks in advance.

Answer 1

This is invalid: FROM socc_matches.mc_id, socc_shots.idmatch FROM socc_matches

And this is incomplete: WHERE socc_matches.mc_teamid = socc_shots

Corrected:

SELECT * 
FROM socc_matches 
INNER JOIN socc_shots ON socc_matches.mc_id = socc_shots.idmatch 
WHERE socc_matches.mc_teamid = socc_shots.mc_teamid
ORDER BY socc_matches.mc_date desc
LIMIT 3

(Replace socc_shots.mc_teamid with whatever that column is called in that table. Or with some value for that matter, e.g. WHERE socc_matches.mc_teamid = 123.)