Mysql inner join error

Question

I have the following problem, im trying to execute and learn about joins as stipulated in W3schools here

Im trying to use a join to get data from 2 tables. Im using the following query:

SELECT rentals.*, cars.tank_capacity, cars.price FROM rentals WHERE mvc_nr = '$mvc' AND active = 'y' INNER JOIN cars ON cars.mvc_nr = rentals.mvc_nr";

both column mvc_nr and active exists in both tables and values correspond.

The value of $mvc = 123456789 in both tables

When I run the query without the WHERE clause I get all the data back and the query executes successfully, but as soon as I add that WHERE clause I get the error

The error reads: syntax error near 'INNER JOIN cars ON cars.mvc_nr = rentals.mvc_nr'

Any help appreciated

try this: SELECT rentals.*, cars.tank_capacity, cars.price FROM rentals INNER JOIN cars ON cars.mvc_nr = rentals.mvc_nr WHERE rental.mvc_nr = '$mvc' AND active = 'y' — 1000111
– 1000111, Commented Aug 28, 2016 at 5:00
INNER JOIN is adding another table to the table you are selecting FROM. The table(s) you are selecting FROM should always come before the WHERE part of the query or else the query won't know the columns you are referencing in the where clause. So it's SELECT [columns] FROM [table] JOIN [table2] ON [foreign columns] WHERE [where something is true]. — Jonathan Kuhn
– Jonathan Kuhn, Commented Aug 28, 2016 at 5:03

1000111 · Accepted Answer · 2016-08-28 05:06:51Z

1

Where clause needs to added after all the JOIN's

SELECT rentals.*, cars.tank_capacity, cars.price FROM rentals INNER JOIN cars ON cars.mvc_nr = rentals.mvc_nr WHERE rentals.mvc_nr = '$mvc' AND rentals.active = 'y'

edited Aug 28, 2016 at 5:06

1000111

13.5k2 gold badges32 silver badges43 bronze badges

answered Aug 28, 2016 at 5:00

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marilee Over a year ago

I have to add WHERE rentals.mvc_nr ='$mvc' and rentals.active='y' if I put WHERE mvc_nr ='$mvc' active = 'y' I get an error mvc_nr is ambiguous

Pரதீப் Over a year ago

Execute my query and check

Marilee Over a year ago

I did,when I modified it, it worked - thanks for guiding me in right direction though +1

1000111 Over a year ago

Since mvc_nr field is common in these two tables so that using it without explicitly referring to it (e.g tableName.columnName) will throw exception like <column_name> is ambiguous @Marilee

Pரதீப் Over a year ago

@1000111 - Was typing it. Thank you

|

Collectives™ on Stack Overflow

Mysql inner join error

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related