CODEWITHSUNDEEP
phpsql
SBD
SBD

Reputation: 446

SQL Error when trying to JOIN tables

I'm trying to run this query: 

$query = $conn->query("SELECT * FROM $table 
    LEFT JOIN vacatures ON bedrijven.id = vacatures.id 
    WHERE id = '$id' 
    AND bedrijfID = '$bedrijf_id'");

But it fails for some reason. I get this error.

Syntax error or access violation: 1066 Not unique table/alias

When I leave the JOIN part, the query is succesful. Why is this happening?

I'm using PHP & PDO to fetch the queries.

Thanks.

EDIT: I wrote the query thanks of the answers given. This is working: 

$query = $conn->query("SELECT * FROM bedrijven 
    LEFT JOIN vacatures v ON bedrijven.id = v.bedrijfID WHERE v.bedrijfID = $bedrijf_id AND v.id = $id");

Upvotes: 0

Views: 70

Answers (3)

hyphen
hyphen

Reputation: 967

Your question isn't super clear, but if you're just trying to do a simple join where the id on table 1 = id on table 2, then the below statement would work. If that's what you're attempting to do, then the AND statement is redundant. Hard to know what you're going for without a clearly defined question with clearly defined variables. Also, use prepared statements as shown below rather than inserting variables directly into your statement. And avoid SELECT * whenever possible. Only select what is absolutely necessary.

$query = $conn->prepare("SELECT * FROM bedrijven b 
    LEFT JOIN vacatures v 
    ON b.id = v.id
    WHERE v.id = :id");
$query->bindValue(':id', $id);
$query->execute();

Upvotes: 1

amarjit singh
amarjit singh

Reputation: 461

The '$id' will be treated as string not as variable.and you need to specify the id as table.id if both of the tables have a field called id.

$query = $conn->query("SELECT * FROM $table LEFT JOIN vacatures ON bedrijven.id =      vacatures.id WHERE $table.id = $id AND bedrijfID = $bedrijf_id");

Upvotes: 0

Blazemonger
Blazemonger

Reputation: 92893

You need to specify one table or the other in WHERE id = '$id', even though they're equal to each other in this case.

You also need to make sure your LEFT JOIN includes $table:

$query = $conn->query("SELECT * FROM $table 
  LEFT JOIN vacatures ON $table.id = vacatures.id 
  WHERE $table.id = '$id' 
  AND bedrijfID = '$bedrijf_id'");

or:

$query = $conn->query("SELECT * FROM bedrijven 
  LEFT JOIN vacatures ON bedrijven.id = vacatures.id 
  WHERE bedrijven.id = '$id' 
  AND bedrijfID = '$bedrijf_id'");

Upvotes: 2

Related Questions