SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Query Help

  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Help

    Hello Everyone,

    Im going crazy trying to figure this out.

    I have 2 tables of vehicle options,

    options & options_extra

    options has

    category, subcategory, veh_option, type

    i would have:
    Select * from options where category = 'auto' AND subcategory = 'car'

    second table
    options_extra

    category, subcategory, veh_option, type, did

    im trying to get a list of all the "options" ( matching category and subcat ) plus all the options_extra that match the cat and sub cat and where did = 189

    i think im getting myself more confused the more i read

    i was trying SELECT options.veh_option, options.type, options.category, options.subcategory FROM options inner JOIN options_extra on options.veh_option = options_extra.veh_option WHERE options.category = 'automobile' AND options.subcategory = 'car' AND options_extra.did = '189'

    any help please.
    TIA

  2. #2
    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)
    Code:
    SELECT veh_option
         , type
         , category
         , subcategory 
      FROM options 
     WHERE category = 'automobile' 
       AND subcategory = 'car' 
    UNION ALL
    SELECT veh_option
         , type
         , category
         , subcategory 
      FROM options_extra 
     WHERE category = 'automobile' 
       AND subcategory = 'car' 
       AND did = '189'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you VERY VERY MUCH. I was going nutz

  4. #4
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have one more question.

    is there a way within this query to get the count of specific "type" - there are 17 different types of options ( audio, standard, comfort etc. ) I was using individual querys to get the number of specific options of each type like (9) audio options, 12 - standard etc

  5. #5
    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 d2wheelz View Post
    is there a way within this query to get the count of specific "type"
    not easily, not within this query

    could you show one of the individual queries you were using
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was doing

    SELECT DISTINCT type, COUNT(type) AS count FROM options WHERE category="automobile" and subcategory='car' group by type order by type

    &

    SELECT DISTINCT type, COUNT(type) AS count FROM options WHERE category="automobile" and subcategory='car' and did='189' group by type order by type

    then if( $rows[type] == "additional" ) { setting a var for each , then adding together

    just seemed like alot of querys

  7. #7
    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)
    in your last post, you show both queries pulling stats from the options table -- did you actually mean the options_extra table for the scond one?

    plus, both those queries will give you counts for all types, i.e. one count per type, so i don't understand why you say you needed a "lot of queries" -- just two of them, one per table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, i was trying to copy and paste real quick before i had to run out,

    what i ment was i had the querys in for the counts, the option table is pretty static

    SELECT DISTINCT type, COUNT(type) AS count FROM options WHERE category='automobile' AND subcategory='car' group by type order by type
    will give me:
    audio 11
    body 4
    comfort 10
    engine_cyl 6
    engine_ltr 13
    extras 22
    fuel_type 6
    int_style 4
    standard 3
    status 3
    title 3
    trans 3
    trans_drive 3
    trans_type 4
    warranty 3

    audio for example then
    if( $rows[type] == "audio" ) {

    if( strpos(" ".$result2[Audio] , $rows[veh_option]) > 0 ) {
    $audio = $audio."<input type='checkbox' name='audio' value='$rows[veh_option]'CHECKED id='$rows[recID]' >$rows[veh_option] <br>";
    } else {
    $audio = $audio."<input type='checkbox' name='audio' value='$rows[veh_option]' id='$rows[recID]' >$rows[veh_option] <br>";
    }

    $aud++;
    if($aud == 3){
    $audio = $audio."</td><td valign='top'>";
    $aud=0;
    }
    }

    knowing I have 11 audio options i can / by 3 and get a nicely formated 4 column check box list.

    dealers wanted to start adding to the options, so i made the options_extra , they all just add the type in as "additional" which was easy to just have that as a seperate checkbox list, and num_rows was my count,

    $count=mysql_num_rows($result1);
    $divid = round($count / 4);

    so when a specific dealer added 60 "additional" options i still had a nicely formated 4 column checkbox list,

    now they wanted to start seperating these into there approperate "type"

    is when i started trying to join the tables, by changing a few of the "additional" to "audio" resetting the aud counter at 3 produced more columns,

    i think im just overcomplicating this in my head. but the objective is to just keep everything aligned to 4 colums

    i wasnt sure if there was a way to run Distinct on the tables after the union, rather then seperatly on both adding them together to get a "count" that i can divide by 4
    and use that as my

    if($addition == $divid){
    $additional = $additional."</td><td valign='top'>";
    $addition=0;
    }

  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)
    you've completely lost me on all that

    by the way, when you have GROUP BY, the DISTINCT becomes redundant and should be removed
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •