SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stuck with query

    Hello everyone,

    I think I'm simply too tired and suffering from some form of SQL block.

    I have the following tables:

    vehicle
    vehicle_id
    manufacturer_id
    model_id
    statu_id
    ...

    wheel
    wheel_id
    manufacturer_id
    model_id
    ...

    tire
    tire_id
    manufacturer_id
    model_id
    ...

    wheel_fitment
    vehicle_id
    wheel_id
    type_id

    tire_fitment
    vehicle_id
    tire_width
    tire_sidewall_height
    tire_rim_diameter
    type_id

    What I need to fing is all of the vehicles that do have a wheel OR a tire that fits. Meaning, all vehicle.vehicle_id that appear in either tire_fitment or wheel_fitment.
    Code:
    select vehicle.vehicle_id
    from vehicle,wheel_fitment
    where vehicle.vehicle_id=wheel_fitment.vehicle_id
    Returns 21695 records in 11.57 seconds.

    Adding "DISTINCT" returns 1038 records in 0.76 seconds.

    The same goes for tires:
    Code:
    select vehicle.vehicle_id
    from vehicle,tire_fitment
    where vehicle.vehicle_id=tire_fitment.vehicle_id
    Returns 4271 records in 2.38 seconds.

    Adding "DISTINCT" returns 1361 records in 0.83 seconds.

    UNION returns the right result but only the vehicle_id:
    Code:
    (
    select vehicle.vehicle_id
    from vehicle,wheel_fitment
    where vehicle.vehicle_id=wheel_fitment.vehicle_id
    )
    union
    (
    select vehicle.vehicle_id
    from vehicle,tire_fitment
    where vehicle.vehicle_id=tire_fitment.vehicle_id
    )
    And I need to get prety much everything in the vehicle table.

    I even tried:
    Code:
    select *
    from vehicle
    where vehicle_id in (
    (
    select vehicle.vehicle_id
    from vehicle,wheel_fitment
    where vehicle.vehicle_id=wheel_fitment.vehicle_id
    )
    union
    (
    select vehicle.vehicle_id
    from vehicle,tire_fitment
    where vehicle.vehicle_id=tire_fitment.vehicle_id
    )
    )
    But it obviously doesn't work (I put it here because it illustrates what I want to do).

    Any ideas on how to do this?

    I'm sorry if it sounds like a basic question but I'm really stuck.
    Luis

  2. #2
    SitePoint Evangelist Aska's Avatar
    Join Date
    Aug 2003
    Location
    Melbourne, Australia
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about if you do this? (If I'm reading your post correctly)
    Code:
    SELECT DISTINCT v.*
    FROM vehicle v, wheel_fitment w, tire_fitment t
    WHERE v.vehicle_id = w.vehicle_id
    OR v.vehicle_id = t.vehicle_id

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you can use subqueries?

    in that case, this is what you want --
    Code:
    select *
    from vehicle
    where 
    vehicle_id in ( select vehicle_id from wheel_fitment )
    or 
    vehicle_id in ( select vehicle_id from tire_fitment )
    rudy

  4. #4
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I'm using MySQL CC or MySQL Front, how do I make sure I cancel the execution of a query?

    Yesterday I tried a couple of queries that took so long I had to kill the program.

    After a couple of hours or site went down and complained there where too many connections to MySQL (something I'd never seen before).
    Luis

  5. #5
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just ran:

    Quote Originally Posted by Aska
    Code:
    SELECT DISTINCT v.*
    FROM vehicle v, wheel_fitment w, tire_fitment t
    WHERE v.vehicle_id = w.vehicle_id
    OR v.vehicle_id = t.vehicle_id
    But it's been runing for over two minutes now...
    Luis

  6. #6
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In reality I don't mind that this query takes long simply because it will only get executed once a day or so. The problem is it's taking WAY TOO LONG...
    Luis

  7. #7
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have any idea why this works directly on MySQL and not when I run it from a PHP script?
    Code:
    SELECT @str_width := tire_width, @str_sidewall_height := tire_sidewall_height
    FROM tire_fitment
    WHERE vehicle_id = 3487 AND tire_rim_diameter = 17;
     
    SELECT manufacturer.name AS manufacturer, model.name AS model, status.name AS status, tire.*
    FROM tire
    LEFT JOIN manufacturer ON manufacturer.manufacturer_id = tire.manufacturer_id
    LEFT JOIN model ON model.model_id = tire.model_id
    LEFT JOIN status ON status.status_id = tire.status_id
    WHERE width = @str_width AND sidewall_height = @str_sidewall_height AND rim_diameter = 17 AND tire.status_id = 2 AND unit_price != 0
    ORDER BY IF(unit_special>0,unit_special,unit_price)
    Luis

  8. #8
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lgomez
    But it's been runing for over two minutes now...
    AFAIK, something like SELECT * should be avoided.
    You're trying to read every single field of the table(s) and - depending on the number of your entries - that can get very time-consuming.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by lgomez
    I just ran: <snip>
    But it's been runing for over two minutes now...
    that's because it's doing a cross join and returning a bazillion records

    i'm sure you did not mean to do a cross join, but that's what you're getting

    rudy

  10. #10
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to get all the vehicle_ids that appear on wheel_fitment and actually have a wheel_id in wheel. How about this:
    Code:
    SELECT DISTINCT year 
    FROM vehicle,wheel_fitment,wheel 
    WHERE vehicle.vehicle_id IN (wheel_fitment.vehicle_id) 
    AND wheel.wheel_id IN (wheel_fitment.wheel_id)
    Its taking forever though... [img]images/smilies/xeye.gif[/img]
    Luis

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ...all the vehicle_ids that appear on wheel_fitment and actually have a wheel_id in wheel
    that's easy:
    Code:
    select distinct 
           wheel.fitment.vehicle_id
      from wheel_fitment
    inner 
      join wheel
        on wheel_fitment.vehicle_id 
         = wheel.vehicle_id
    perhaps you might benefit from a refresher tutorial: SQL Join

    rudy

  12. #12
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's late (about midnight here in Miami) and I want to go home. But wanted to thank you for your help before leaving.

    Even though I want to check the results again tomorrow when fully awake, here's what I tried based on your sugention:
    Code:
    (select distinct wheel_fitment.vehicle_id
    from wheel_fitment,vehicle
    inner join wheel on wheel_fitment.vehicle_id = vehicle.vehicle_id)
    union
    (select distinct tire_fitment.vehicle_id
    from tire_fitment,vehicle
    inner join tire on tire_fitment.vehicle_id = vehicle.vehicle_id)
    It seems to be exactly what I need and it proves I'd really benefit from the tutorial you sugested .

    Thank you and good night,
    Luis

  13. #13
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lgomez
    ... and I want to go home.
    well, who doesn't?
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  14. #14
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, here's a bomb for you... lets see if someone is bored enough to go throuhg this:
    PHP Code:
    echo "<script language=\"JavaScript\" src=""\"\script\DynamicOptionList.js\"></script>\n";
    echo 
    "<script language=\"JavaScript\">\n";
    echo 
    "var mk = new DynamicOptionList(\"make\",\"year\");\n";
    echo 
    "mk.addOptions(\"Year\",\"Make\",\"Make\");\n";
    echo 
    "mk.setDefaultOption(\"Year\",\"Make\");\n";
    $year_sql=" (select distinct year
             from wheel_fitment,vehicle
             inner join wheel on wheel_fitment.vehicle_id = vehicle.vehicle_id)
             union
             (select distinct year
             from tire_fitment,vehicle
             inner join tire on tire_fitment.vehicle_id = vehicle.vehicle_id)
             ORDER BY year DESC"
    ;
    $year_rs=mysql_query($year_sql,$db);
    while(
    $year=mysql_fetch_array($year_rs)){
     echo 
    "mk.addOptions(";
     echo 
    "\"".$year['year']."\",\"Make\",\"Make\"";
     
    $make_sql=" (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
               FROM vehicle,tire_fitment
               inner join tire on tire_fitment.vehicle_id = vehicle.vehicle_id
               LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
               WHERE year = " 
    $year['year'] . ")
               UNION
               (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
               FROM vehicle,wheel_fitment
               inner join wheel on wheel_fitment.vehicle_id = vehicle.vehicle_id
               LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
               WHERE year = " 
    $year['year'] . ")
               ORDER BY manufacturer"
    ;
     
    $make_rs=mysql_query($make_sql,$db);
     while(
    $make=mysql_fetch_array($make_rs)){
      echo 
    ",\"".$make['manufacturer']."\",\"".$make['manufacturer_id']."\"";
     }
     echo 
    ");\n";
     echo 
    "mk.setDefaultOption(\"".$year['year']."\",\"Make\");\n";
    }
    echo 
    "mk.setDefaultOption(\"Year\",\"Make\");\n";
    echo 
    "var md = new DynamicOptionList(\"model\",\"year\",\"make\");\n";
    echo 
    "md.addOptions(\"Year|Make\",\"Model\",\"Model\");";
    echo 
    "md.setDefaultOption(\"Year|Make\",\"Model\");";
    $year_sql=" (select distinct year
             from wheel_fitment,vehicle
             inner join wheel on wheel_fitment.vehicle_id = vehicle.vehicle_id)
             union
             (select distinct year
             from tire_fitment,vehicle
             inner join tire on tire_fitment.vehicle_id = vehicle.vehicle_id)
             ORDER BY year DESC"
    ;
    $year_rs=mysql_query($year_sql,$db);
    while(
    $year=mysql_fetch_array($year_rs)){
     
    $make_sql=" (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
               FROM vehicle,tire_fitment
               inner join tire on tire_fitment.vehicle_id = vehicle.vehicle_id
               LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
               WHERE year = " 
    $year['year'] . ")
               UNION
               (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
               FROM vehicle,wheel_fitment
               inner join wheel on wheel_fitment.vehicle_id = vehicle.vehicle_id
               LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
               WHERE year = " 
    $year['year'] . ")
               ORDER BY manufacturer"
    ;
     
    $make_rs=mysql_query($make_sql,$db);
     echo 
    "md.addOptions(\"".$year['year']."|Make\",\"Model\",\"Model\");\n";
     while(
    $make=mysql_fetch_array($make_rs)){
      
    $model_sql=" (SELECT manufacturer.name AS manufacturer,model.name AS model, vehicle.model_id
                FROM vehicle,tire_fitment
                inner join tire on tire_fitment.vehicle_id = vehicle.vehicle_id
                LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
                LEFT JOIN model ON model.model_id = vehicle.model_id
                WHERE vehicle.year = " 
    $year['year'] . " AND manufacturer.manufacturer_id = " $make['manufacturer_id'] . "
                ORDER BY year)
                UNION
                (SELECT manufacturer.name AS manufacturer,model.name AS model, vehicle.model_id
                FROM vehicle,wheel_fitment
                inner join wheel on wheel_fitment.vehicle_id = vehicle.vehicle_id
                LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
                LEFT JOIN model ON model.model_id = vehicle.model_id
                WHERE vehicle.year = " 
    $year['year'] . " AND manufacturer.manufacturer_id = " $make['manufacturer_id'] . "
                ORDER BY year)"
    ;
      
    $model_rs=mysql_query($model_sql,$db);
      echo 
    "md.addOptions(";
      echo 
    "\"".$year['year']."|".$make['manufacturer_id']."\",\"Model\",\"Model\"";
      while(
    $model=mysql_fetch_array($model_rs)){
       echo 
    ",\"".$model['model']."\",\"".$model['model_id']."\"";
      }
      echo 
    ");\n";
     }
     echo 
    "md.setDefaultOption(\"".$year['year']."|Make\",\"Model\");\n";
    }
    echo 
    "function init() {\n";
    echo 
    " var rail_vehicle_selector = document.rail_vehicle_selector;\n";
    echo 
    " mk.init(rail_vehicle_selector);\n";
    echo 
    " md.init(rail_vehicle_selector);\n";
    echo 
    " }\n";
    echo 
    "</script>\n"
    The poin is to generate a vehicle selection tool composed of three dropdowns that work together to show the vehicles that have products that fit them. These may be wheels and/or tires.

    I tested this script but it takes forever to process.

    I'll keep working on it to see if I find a better way.

    Have fun...

    By the way, you can see a working version of this here but this one doesn't show vehicles that actually have a product (which may have been deleted) but do appear in the fitment tables.
    NOTE: The only difference is in the queries:
    PHP Code:
    echo "<script language=\"JavaScript\" src=""\"\script\DynamicOptionList.js\"></script>\n";
    echo "<script language=\"JavaScript\">\n";
    echo "var mk = new DynamicOptionList(\"make\",\"year\");\n";
    echo "mk.addOptions(\"Year\",\"Make\",\"Make\");\n";
    echo "mk.setDefaultOption(\"Year\",\"Make\");\n";
    $year_sql=" (SELECT year 
       FROM vehicle,tire_fitment
       WHERE vehicle.vehicle_id=tire_fitment.vehicle_id
       ORDER BY year DESC)
       UNION
       (SELECT year
       FROM vehicle,wheel_fitment
       WHERE vehicle.vehicle_id=wheel_fitment.vehicle_id
       ORDER BY year DESC)";
    $year_rs=mysql_query($year_sql,$db);
    while($year=mysql_fetch_array($year_rs)){
     echo "mk.addOptions(";
     echo "\"".$year['year']."\",\"Make\",\"Make\"";
     $make_sql=" (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
        FROM vehicle,tire_fitment
        LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
        WHERE vehicle.vehicle_id=tire_fitment.vehicle_id AND year = " . $year['year'] . "
        ORDER BY manufacturer)
        UNION
        (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
        FROM vehicle,wheel_fitment
        LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
        WHERE vehicle.vehicle_id=wheel_fitment.vehicle_id AND year = " . $year['year'] . "
        ORDER BY manufacturer)";
     $make_rs=mysql_query($make_sql,$db);
     while($make=mysql_fetch_array($make_rs)){
      echo ",\"".$make['manufacturer']."\",\"".$make['manufacturer_id']."\"";
     }
     echo ");\n";
     echo "mk.setDefaultOption(\"".$year['year']."\",\"Make\");\n";
    }
    echo "mk.setDefaultOption(\"Year\",\"Make\");\n";
    echo "var md = new DynamicOptionList(\"model\",\"year\",\"make\");\n";
    echo "md.addOptions(\"Year|Make\",\"Model\",\"Model\");";
    echo "md.setDefaultOption(\"Year|Make\",\"Model\");";
    $year_sql=" (SELECT year 
       FROM vehicle,tire_fitment
       WHERE vehicle.vehicle_id=tire_fitment.vehicle_id
       ORDER BY year DESC)
       UNION
       (SELECT year
       FROM vehicle,wheel_fitment
       WHERE vehicle.vehicle_id=wheel_fitment.vehicle_id
       ORDER BY year DESC)";
    $year_rs=mysql_query($year_sql,$db);
    while($year=mysql_fetch_array($year_rs)){
     $make_sql=" (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
        FROM vehicle,tire_fitment
        LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
        WHERE vehicle.vehicle_id=tire_fitment.vehicle_id AND year = " . $year['year'] . "
        ORDER BY manufacturer)
        UNION
        (SELECT manufacturer.name AS manufacturer, vehicle.year AS year, vehicle.manufacturer_id
        FROM vehicle,wheel_fitment
        LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
        WHERE vehicle.vehicle_id=wheel_fitment.vehicle_id AND year = " . $year['year'] . "
        ORDER BY manufacturer)";
     $make_rs=mysql_query($make_sql,$db);
     echo "md.addOptions(\"".$year['year']."|Make\",\"Model\",\"Model\");\n";
     while($make=mysql_fetch_array($make_rs)){
      $model_sql=" (SELECT manufacturer.name AS manufacturer,model.name AS model, vehicle.model_id
          FROM vehicle,tire_fitment
          LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
          LEFT JOIN model ON model.model_id = vehicle.model_id
          WHERE vehicle.vehicle_id=tire_fitment.vehicle_id AND vehicle.year = " . $year['year'] . " AND manufacturer.manufacturer_id = '" . $make['manufacturer_id'] . "'
          ORDER BY year)
          UNION
          (SELECT manufacturer.name AS manufacturer,model.name AS model, vehicle.model_id
          FROM vehicle,wheel_fitment
          LEFT JOIN manufacturer ON manufacturer.manufacturer_id = vehicle.manufacturer_id
          LEFT JOIN model ON model.model_id = vehicle.model_id
          WHERE vehicle.vehicle_id=wheel_fitment.vehicle_id AND vehicle.year = " . $year['year'] . " AND manufacturer.manufacturer_id = '" . $make['manufacturer_id'] . "'
          ORDER BY year)";
      $model_rs=mysql_query($model_sql,$db);
      echo "md.addOptions(";
      echo "\"".$year['year']."|".$make['manufacturer_id']."\",\"Model\",\"Model\"";
      while($model=mysql_fetch_array($model_rs)){
       echo ",\"".$model['model']."\",\"".$model['model_id']."\"";
      }
      echo ");\n";
     }
     echo "md.setDefaultOption(\"".$year['year']."|Make\",\"Model\");\n";
    }
    echo "function init() {\n";
    echo " var rail_vehicle_selector = document.rail_vehicle_selector;\n";
    echo " mk.init(rail_vehicle_selector);\n";
    echo " md.init(rail_vehicle_selector);\n";
    echo " }\n";
    echo "</script>\n";
    ?>
    </head>
    <body onLoad="init()">
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" name="rail_vehicle_selector">
    <select name="year" onChange="mk.populate();md.populate();">
     <option value="Year">Year</option>
     <?php
     $year_sql
    =" (SELECT year 
        FROM vehicle,tire_fitment
        WHERE vehicle.vehicle_id=tire_fitment.vehicle_id
        ORDER BY year DESC)
        UNION
        (SELECT year
        FROM vehicle,wheel_fitment
        WHERE vehicle.vehicle_id=wheel_fitment.vehicle_id
        ORDER BY year DESC)"
    ;
     
    $year_rs=mysql_query($year_sql,$db);
     while(
    $year=mysql_fetch_array($year_rs)){
      echo 
    "   <option value=\"" $year['year'] . "\">" $year['year'] . "</option>\n";
     }
    You may ask yourself why would I want to keep the vehicle in the fitment table(s) if there's no product for it? The answer is that fitment data doesn't change and may be reused in the future (ie: we may carry the product again in the future).
    Luis


Bookmarks

Posting Permissions

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