Jamie McAllister Jamie McAllister - 5 months ago 15
MySQL Question

PDO multiple inner joins appear to be looping

I'm using

INNER JOIN
for the first time and I'm a little confused.

I have 3 tables:

dog
client
dogclient


I'm trying to get information from both the
client
and
dog
tables for every record in the
dogclient
table. when i do, the result is this:

[{"ID":"1","Name":"Sparky","Forename":"Jamie","Surname":"McAllister"},
{"ID":"1","Name":"Sparky","Forename":"Jamie","Surname":"McAllister"},
{"ID":"2","Name":"Scruff","Forename":"Jamie","Surname":"McAllister"},
{"ID":"2","Name":"Scruff","Forename":"Jamie","Surname":"McAllister"}]


The function i'm using to make this query is:

function getAll(){

$conn = self::connect();

$stmt = $conn->prepare("SELECT dog.ID, dog.Name, client.Forename, client.Surname FROM dogclient INNER JOIN client ON dogClient.ClientID INNER JOIN dog ON dogclient.DogID");
$stmt->execute();

// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$response = $stmt->fetchAll();

return $response;


Can anyone see where I've gone wrong?

or, does anyone know a better way to do this?

if it helps, there are only 2 dogs in the
dog
table, one client in the
client
table, and 2 records in the
dogclient
table, so i was expecting 2 results

Answer

Try this:

INNER JOIN client ON dogClient.ClientID = client.ClientID INNER JOIN dog ON dogclient.DogID = dog.DogID
Comments