SQL joins confusion

Question

what is problem in this join??? The console says

Error Code: 1051
Unknown table 'invlabtes'


SELECT invlabtes.*, invgatepass.BuiltySupp FROM invlabtes FULL JOIN invgatepass ON(invlabtes.GatePassNO=invgatepass.InwardNo AND invlabtes.GatePassDate=invgatepass.EntryDate AND invlabtes.VehicleNo=invgatepass.VehicleNo) WHERE  invlabtes.Code='*****'

If I replace the Full join with inner join then the query returns an entry.

All I want is to fetch data from invlabtes table based on invlabtes.Code even if the join condition fails and invgatepass has no matching builtySupp

Uhhh.. does the table `invlabtes` exists in your db? Maybe misspelled or you maybe selected/configured the wrong db? Local or remote, maybe you forgot to add the table in the remote db.. — Sven van Zoelen, Nov 16 '13 at 08:55

score 1 · Answer 1 · answered Nov 16 '13 at 08:57

1

Mysql doesn't support FULL keyword and turns it into table alias. So your invlabtes table is now named FULL.

answered Nov 16 '13 at 08:57

Marek

7,337
1
22
33

score 0 · Answer 2 · answered Nov 16 '13 at 09:55

If you want to return data always from invlabtes table even if it doesn't has match in invgatepass, try using LEFT JOIN instead, as Mysql doesn't supports full joins

SELECT 
invlabtes.*, invgatepass.BuiltySupp 
FROM 
invlabtes LEFT JOIN 
invgatepass 
ON
invlabtes.GatePassNO=invgatepass.InwardNo
AND 
invlabtes.GatePassDate=invgatepass.EntryDate 
AND 
invlabtes.VehicleNo=invgatepass.VehicleNo 
WHERE  
invlabtes.Code='*****'

SQL joins confusion

2 Answers2