By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,025 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,025 IT Pros & Developers. It's quick & easy.

Looking for help with MySQL 4.0.24 LEFT JOIN

P: 4
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT C.id
  2. FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id)
  3. 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.


Expand|Select|Wrap|Line Numbers
  1. SELECT c.*, ma.makeName, mo.modelName FROM cars c, makes ma, models mo 
  2. where c.dealer_id=12 and c.make = ma.makeId and c.model = mo.modelId and not exists (
  3. select car_id from newspaper n 
  4. where c.id = n.car_id and n.submit_num =10
  5. );
(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.

Expand|Select|Wrap|Line Numbers
  1. SELECT id
  2. FROM cars
  3. WHERE EXISTS (
  4. SELECT * FROM newspaper
  5. WHERE id = car_id
  6. );
Or:

Expand|Select|Wrap|Line Numbers
  1. SELECT id FROM cars 
  2. WHERE id IN(
  3. SELECT car_id FROM newspaper
  4. );
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT C.id
  2. FROM cars C LEFT OUTER JOIN newspaper N USING (C.id=N.car_id)
  3. 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.
Nov 17 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.