Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: multiple left outer join problem

    Hi
    I have to use multiple left outer join with this statement:

    SELECT AUTO.USER_ID as USER_ID, PRODUCERS.BO_NAME as PRODUCER_NAME,AUTO.AUTO_ID as AUTO_ID,AUTO.DATE_ADDED as DATE_ADDED, EQUIPMENT_IN_USE.EQ_IDs as EQ_IDs,AUTO.MODEL as MODEL,AUTO.MILEAGE as MILEAGE, AUTO.PRICE as PRICE, AUTO.DESCRIPTION as DESCRIPTION, AUTO.RATE as RATE,
    AUTO.VMAX as VMAX,AUTO.KM as KM,AUTO.FUEL_USE as FUEL_USE,AUTO.FIRST_OWNER as FIRST_OWNER,
    PRODUCERS.BO_ID as PRODUCER_ID, MOTORS.BO_ID as MOTOR_ID, COLORS.BO_ID as COLOR_ID, BODIES.BO_ID as BODY_ID, REGIONS.BO_ID as REGION_ID, STATES.BO_ID as STATE_ID,
    OFFERS.BO_ID as OFFER_ID,AIRBAGS.BO_ID as AIRBAGS_ID, YEARS.BO_ID as YEAR_PRODUCED_ID, DOORS.BO_ID as DOORS_ID

    FROM AUTO left outer join equipment_in_use on auto.auto_id=equipment_in_use.auto_id
    left outer join producers on auto.PRODUCER_ID=PRODUCERS.BO_ID
    left outer join motors on AUTO.MOTOR_ID=MOTORS.BO_ID
    left outer join colors on AUTO.COLOR_ID=COLORS.BO_ID
    left outer join bodies on AUTO.body_ID=bodies.BO_ID
    left outer join regions on AUTO.region_ID=regions.BO_ID
    left outer join doors on AUTO.door_ID=doors.BO_ID
    left outer join states on AUTO.state_ID=states.BO_ID
    left outer join offers on AUTO.offer_ID=offers.BO_ID
    left outer join airbags on AUTO.airbag_ID=airbags.BO_ID
    left outer join years on AUTO.year_ID=years.BO_ID

    WHERE AUTO.USER_ID=1

    but have got an error:
    Unknown table producers in fields list

    What is wrong in that statement??
    Cheers
    Bartek Wasko

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    Table names are CAse SeNsITive. "PRODUCERS" is not "producers."

    Also... that query, when it does run, is gonna be killer (as in "bad"). If you can possibly humanly do it, simplify that query into several smaller ones!
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    Thanks
    That statement is cached so the situation isn't so bad I hope...
    Bart

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    Definitely look at the query-plan that MySQL produces and see what you think. I get very nervous about "big complex queries" and prefer to execute a series of smaller ones that culminate in a temporary table... unless I determine by actual examination that MySQL has in fact come up with the same thing. I've definitely come up with both results: "ooh, brilliant!" and "aww, MySQL, you are so full of <ahem>.."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •