Godaddy server using MySQL 4.0.24
I’m trying a left join to obtain id’s in table A(cars) that are NOT in car_id in table B(newspaper):
*This is a cut down version to simplify testing. Full version is posted towards the end.
Expand|Select|Wrap|Line Numbers
- SELECT C.id
- FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id)
- WHERE N.car_id IS NOT NULL;
Please keep in mind I’m a total noob when it comes to SQL so if you have time please include a SQL code example in your response.
---------------------------------------------------------
Ok, full story for anyone interested.
I’m trying to compare two vehicle info tables.
One that is a listing of a dealers cars, the other is a listing of the dealers cars currently in our newspaper. I’m using this to populate Ajax drag n drop columns so they can simply drag vehicles into the “in” column and submit them us.
This is the query I’m using in MySQL 5.0 which works fine.
Expand|Select|Wrap|Line Numbers
- SELECT c.*, ma.makeName, mo.modelName FROM cars c, makes ma, models mo
- where c.dealer_id=12 and c.make = ma.makeId and c.model = mo.modelId and not exists (
- select car_id from newspaper n
- where c.id = n.car_id and n.submit_num =10
- );
dealer_id and submit_num are variables in the script. I just put the values in for testing / example.
After having issues on our live server I cut it down to it’s simplest form to try and figure out exactly what the problem was.
Expand|Select|Wrap|Line Numbers
- SELECT id
- FROM cars
- WHERE EXISTS (
- SELECT * FROM newspaper
- WHERE id = car_id
- );
Expand|Select|Wrap|Line Numbers
- SELECT id FROM cars
- WHERE id IN(
- SELECT car_id FROM newspaper
- );
So I searched a bit and found a query that fits what I need and well, here it is.
Expand|Select|Wrap|Line Numbers
- SELECT C.id
- FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id)
- WHERE N.car_id IS NOT NULL;
MySQL said:I’ve searched LEFT JOIN on the MySQL docs and just cant see what I’m doing wrong.
Documentation #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 '.id=N.car_id)
WHERE N.car_id IS NOT NULL' at line 2
Any advice would be greatly appreciated.
Just please keep in mind I’m nearly SQL illiterate so please include an example if you have time.