471,889 Members | 2,240 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,889 software developers and data experts.

Looking for help with MySQL 4.0.24 LEFT JOIN

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
  4. SELECT * FROM newspaper
  5. WHERE id = car_id
  6. );

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
0 2139

Post your reply

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

Similar topics

13 posts views Thread by aaron | last post: by
6 posts views Thread by Xenophobe | last post: by
4 posts views Thread by NotGiven | last post: by
reply views Thread by Phil Powell | last post: by
7 posts views Thread by Jim | last post: by
4 posts views Thread by Diamondback | last post: by
1 post views Thread by cmgmyr | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.