Quick and dirty version.
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. - SELECT C.id
-
FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id)
-
WHERE N.car_id IS NOT NULL;
I’m sure it’s just syntax errors as I don’t know SQL so well but I can’t for the life of me get it working.
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.
- 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
-
);
(
I’m sure it’s nasty code as it’s all pieced together via examples I could find online)
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.
- SELECT id
-
FROM cars
-
WHERE EXISTS (
-
SELECT * FROM newspaper
-
WHERE id = car_id
-
);
Or:
- SELECT id FROM cars
-
WHERE id IN(
-
SELECT car_id FROM newspaper
-
);
I read on php.net that EXISTS is only supported from 4.1 on. And that earlier versions of MySQL should try rewriting the query using a LEFT JOIN.
So I searched a bit and found a query that fits what I need and well, here it is.
- SELECT C.id
-
FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id)
-
WHERE N.car_id IS NOT NULL;
I’m getting this error.
MySQL said:
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
I’ve searched LEFT JOIN on the MySQL docs and just cant see what I’m doing wrong.
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.
