CODEWITHSUNDEEP
mysqlsqlinner-join
Furlab
Furlab

Reputation: 43

INER JOIN error

I would like to go for Inner join and getting the bellow error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN ( SELECT Chalanits.Chalanit_partno, Chalanits.Chalanit_qty FROM Chala' at line 7

SELECT Cuspoit.`Cuspoit_partno`, Cuspoit.`Cuspoit_unitprice`, Chalanits.Chalanit_qty
FROM Cuspoit 
WHERE Cuspoit.`Cuspoit_cuspoid` =
(
SELECT Chalanits.Chalanit_cuspoid FROM Chalanits WHERE Chalanits.Chalanit_chalanid='CX-1306001' LIMIT 1
)
INNER JOIN (
SELECT Chalanits.Chalanit_partno, Chalanits.Chalanit_qty
FROM Chalanits 
WHERE Chalanits.Chalanit_chalanid='CX-1306001')
ON Cuspoit.`Cuspoit_partno`= Chalanits.Chalanit_partno

Can anyone help me to find the mistake??

Upvotes: 0

Views: 203

Answers (4)

MatheusOl
MatheusOl

Reputation: 11835

The error is because the WHERE clause should come after INNER JOIN.

But, beside that, seems you are using sub-queries where you really don't need it.

SELECT Cuspoit.`Cuspoit_partno`, Cuspoit.`Cuspoit_unitprice`, Chalanits.Chalanit_qty
FROM Cuspoit 
INNER JOIN Chalanits ON Cuspoit.`Cuspoit_partno`= Chalanits.Chalanit_partno
WHERE Cuspoit.`Cuspoit_cuspoid` = (
    SELECT Chalanits.Chalanit_cuspoid FROM Chalanits WHERE Chalanits.Chalanit_chalanid='CX-1306001' LIMIT 1
)
AND Chalanits.Chalanit_chalanid='CX-1306001'

Also, the subquery that still remains looks really odd, because it gets a possibly random register (which depends on PKs and UKs) because of the LIMIT with no sort. If you want Chalanits rows with same cuspoid and partno of Cuspoit table, then you want this:

SELECT Cuspoit.`Cuspoit_partno`, Cuspoit.`Cuspoit_unitprice`, Chalanits.Chalanit_qty
FROM Cuspoit 
INNER JOIN Chalanits
    ON Cuspoit.`Cuspoit_partno`= Chalanits.Chalanit_partno
       AND Cuspoit.`Cuspoit_cuspoid = Chalanits.Chalanit_cuspoid
WHERE Chalanits.Chalanit_chalanid='CX-1306001'

Upvotes: 1

David Scott
David Scott

Reputation: 1084

You cannot have an INNER JOIN after the WHERE Clause

WHERE Cuspoit.`Cuspoit_cuspoid` =
(
SELECT Chalanits.Chalanit_cuspoid FROM Chalanits WHERE Chalanits.Chalanit_chalanid='CX-1306001' LIMIT 1
)
INNER JOIN (

Here is the error

Upvotes: 0

HLGEM
HLGEM

Reputation: 96600

Where clauses come after all of the joins. And when you use a subquery inteh where clause try using IN instead of =.

Upvotes: 1

rpasianotto
rpasianotto

Reputation: 1413

Use that SQL: 

SELECT Cuspoit.`Cuspoit_partno`, Cuspoit.`Cuspoit_unitprice`, Chalanits.Chalanit_qty
FROM Cuspoit
INNER JOIN (
SELECT Chalanits.Chalanit_partno, Chalanits.Chalanit_qty
FROM Chalanits 
WHERE Chalanits.Chalanit_chalanid='CX-1306001')
ON Cuspoit.`Cuspoit_partno`= Chalanits.Chalanit_partno 
WHERE Cuspoit.`Cuspoit_cuspoid` =
(
SELECT Chalanits.Chalanit_cuspoid FROM Chalanits WHERE Chalanits.Chalanit_chalanid='CX-    1306001' LIMIT 1
)

You have inverted the WHERE and THE INNER JOIN comand

Upvotes: 1

Related Questions