CODEWITHSUNDEEP
mysqlsql
user2334436
user2334436

Reputation: 939

Left join 3 tables error #1066

I'm trying to left join 3 table but getting the following error

"#1066 - Not unique table/alias: 'uid'"

I tried some of the suggestions i found online such as using an alias tbl_add_unit.uid AS UnitUID but that didn't work.

My code is:

SELECT tbl_add_unit.uid, tbl_add_rent.r_unit_no, tbl_add_electricity_bill.unit_no
FROM tbl_add_unit, tbl_add_rent, tbl_add_electricity_bill
LEFT JOIN tbl_add_unit.uid ON tbl_add_rent.r_unit_no
LEFT JOIN tbl_add_unit.uid ON tbl_add_electricity_bill.unit_no;

Upvotes: 0

Views: 77

Answers (4)

Uueerdo
Uueerdo

Reputation: 15941

The general syntax of joins should be something like...

SELECT some stuff
FROM table1
JOIN table2 ON table1.blahA = table2.blahB
JOIN table3 ON table2.blahC = table3.blahD
;

LEFT JOIN keeps the data from the left side of the join even when there are no matches on the right, INNER JOIN only keeps joined data.

Upvotes: 0

Jesse
Jesse

Reputation: 873

You are close, let's aim for something more akin to:

SELECT tbl_add_unit.uid, tbl_add_rent.r_unit_no, tbl_add_electricity_bill.unit_no
FROM tbl_add_unit
LEFT JOIN tbl_add_rent ON tbl_add_unit.uid = tbl_add_rent.r_unit_no
LEFT JOIN tbl_add_electricity_bill ON tbl_add_unit.uid = 
tbl_add_electricity_bill.unit_no;

That assumes what is in the joins is true, so: tbl_add_unit.uid = tbl_add_rent.r_unit_no and tbl_add_unit.uid = tbl_add_electricity_bill.unit_no. If that is true, you're good to go.

Upvotes: 1

Saravana Kannadasan
Saravana Kannadasan

Reputation: 160

In your query, it looks like you have tables separated by comma. I would assume you would just like to join these tables to fetch information. You can just use JOIN statement and specify aliases for each of these tables.

SELECT 
   tblAddUnit.uid, tblAddRent.r_unit_no, tblAddElectricityBill.unit_no
FROM 
   tbl_add_unit tblAddUnit 
LEFT JOIN 
   tbl_add_rent tblAddRent ON
   tblAddUnit.uid ON tblAddRent.r_unit_no
LEFT JOIN 
   tbl_add_electricity_bill tblAddElectricityBill ON 
   tblAddElectricityBill.unit_no = tblAddUnit.uid;

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

Ditch the old-school comma syntax for the join operation. Those commas are equivalent to JOIN keyword. And typically, there's a condition used to match the tables, specified in a ON clause.

Looks like maybe we were after a result set something like the result that would be returned by a query like this:

 SELECT u.uid
      , r.r_unit_no
      , b.unit_no
   FROM tbl_add_unit u
   LEFT
   JOIN tbl_add_rent r
     ON r.r_unit_no = u.uid
   LEFT
   JOIN tbl_add_electricity_bill b
     ON b.unit_no = u.uid
  WHERE ...
  ORDER
     BY ...

What was the question?

Upvotes: 2

Related Questions