Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Unanswered: Relatively easy query. Select from selected results?

    I want to select from within selected results. Sort of nested selects.
    I also want to be able to do the nested select iteration an arbitrary number of times (if possible).

    What I mean by "select from selected results" is that I have the following table:
    type_id | country_id
    13 | 1
    13 | 4
    13 | 7
    13 | 8
    14 | 2
    14 | 9
    14 | 3
    15 | 7
    15 | 8
    15 | 9
    16 | 1
    16 | 3
    17 | 1
    17 | 9
    Here for example, I SELECT type_id WHERE country_id=1 and I get 13,16 and 17 as result.

    Next I want to select from another table

    type_id | person_id
    ...
    13 | 11
    13 | 14
    13 | 17
    13 | 20
    14 | 12
    14 | 39
    14 | 43
    15 | 11
    15 | 43
    16 | 12
    17 | 45
    17 | 67
    ...
    I want to SELECT type_id FROM table WHERE type_id=11 AND type_id=20. But I only want to select from within the first results (13, 16, 17)
    That is, I want to have only 13 (which contains both 11 and 20) as result, and not 13 and 15 (where 15 only contains 11).

    After this I may have to do another iteration on other similar tables.

    Does this make sense?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Two methods;

    1) JOIN
    Code:
    SELECT p.person_id
    FROM   people p
     INNER
      JOIN c.countries c
        ON p.type_id = c.type_id
    WHERE  c.country_id = 1
    2) Subselect
    Code:
    SELECT person_id
    FROM   people
    WHERE  type_id IN (SELECT type_id FROM countries WHERE county_id = 1)
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2008
    Posts
    7
    Quote Originally Posted by georgev
    Two methods;

    1) JOIN
    [CODE]
    SELECT p.person_id
    FROM people p
    INNER
    JOIN c.countries c
    ON p.type_id = c.type_id
    WHERE c.country_id = 1
    Thanks, I am already using this query. How can I use the results from this query in my next query? That is, I only want results in my next query that are WITHIN the results from the first query.
    Sorry for my bad explanation.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using a derived table is one option
    Code:
    SELECT *
    FROM  (
           <insert first select query>
          ) As [some_alias]
    WHERE  some_field = 'some value'
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2008
    Posts
    7
    Thanks, georgev.

    I think I'm still not there yet.
    Let me rephrase:
    I have 5 mapping (many-to-many) tables.
    For example:
    table1: car_id|country_id
    table2: car_id|color_id
    table3: car_id|location_id
    table4: car_id|previous_owner_id
    table5: car_id|type_id
    Now I want to select a german car, which is black, located in AZ, has been owned by ACME Corp, and is of type SUV

    Obviously, I need to do some JOINing to connect the country_id with 'Germany' and color_id with 'black' etc. (I have tables for Country and Color etc). I think I can hadle that.
    The big problem is; how do I select first all german cars, then from that result select black cars, then select cars located in AZ, etc.
    I need to narrow my search through up to 5 iterations.
    And to make it more complex, I sometimes want to make only 2 or 3 iterations, and the search order is arbitrary (maybe 'location' or 'type' comes first).

    It this possible?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you doing this purely in a MySQL context, or are you doing this through a front end, such as a web based front end in PHP or ASP or PERL etc, or a conventional programming environment such as VB/VC/C#, or somethign else lkike Delphi or Access?

    I've seen an example that does something very very similar to this in PHP, and I think using AJAX to smooth the UI
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I’ll guess you’re using PHP with MySQL but this applies to anything...

    You build the web screen and have a parameter for each field ie $carType=12.
    The order by would just be a list of field names – they can be displayed prettier though.
    If a parameter isn’t selected then you just default to 0 or something.

    When the PHP runs it just builds the SQL:

    Code:
    $sql = select c.car_id, c.name from Cars c where 1=1”
    Then for each parameter you do the following to build up the SQL.

    Code:
    If ( $carType > 0 ) { $sql =”$sql and exists( select 1 from Table5 t where t.car_id = c.car_id and t.type_id = $carType )” }
    Finally add the order by …

    Code:
    If ( $orderBy > ‘’ ) { $sql =”$sql order by $orderBy” } else { $sql =”$sql order by name” }
    Then run the final SQL through the database and display the results. If the user clicks on the car name then you just supply all the parameters known about that car as info. You might want to limit the number of rows returned in case the user enters no parameters (use limit). If you ask me you have too many id’s and lookups making things more complex than the whole thing should be. Personally I’d put all the lookups in a single table with a type field but I understand others don’t like this approach. It would also help to have some proper table names rather than having Table5 etc.

    Mike

  8. #8
    Join Date
    Apr 2008
    Posts
    7
    Quote Originally Posted by healdem
    are you doing this purely in a MySQL context, or are you doing this through a front end, such as a web based front end in PHP or ASP or PERL etc, or a conventional programming environment such as VB/VC/C#, or somethign else lkike Delphi or Access?

    I've seen an example that does something very very similar to this in PHP, and I think using AJAX to smooth the UI
    I'm using PHP and AJAX.
    I'm very interested to see that example.


    Quote Originally Posted by mike_bike_kite
    I’ll guess you’re using PHP with MySQL but this applies to anything...
    Thanks. I'll dwell into your solution now.
    To be specific, I pass the paramters to be searched through the URL.
    Code:
    search.php?country=Germany,USA&color=black&location=AZ,NY,CA&previous_owner=Acme%20Corp&type=SUV,Pickup
    (note that each parameter can have several values)
    From this string, I extract the values nicely into their separate arrays.
    $country[] => {Germany, USA}
    $color[] => {Black}
    $location[] => {AZ, NY, CA}
    etc.
    And THEN comes the SQL struggle.
    I'm able to JOIN the Country table with table1 to match country.name with country.id (and so on).
    It's the nested selects that are bothering me.

    I'll look at your solution now.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's wrong with using 5 sets of JOINS with WHERE clauses for the filtering; this allows you to query for any combination of levels based on the where clause... Right?
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    What's wrong with using 5 sets of JOINS with WHERE clauses for the filtering; this allows you to query for any combination of levels based on the where clause... Right?
    wrong

    you cannot join 5 many-to-many tables and have any hope whatsoever of returning correct results since in order to satisfy the search requirement you might need more than one row from any of them

    do a search for "cross join effects"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2008
    Posts
    7
    Any other suggestions? I'm short of ideas.

    To the above:
    I'm planning only to join each many-to-many with it's respective table. NOT joining all of the many-to-many among themselves.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As I read it, its not really a SQL problem, its a user interface problem using linked list boxes
    I think its going to be somehting like Fords of Winsford

    users selects a car maker: next linked list box filled with models from that car maker
    user selects a model: next linked list box fills with variants of that model (say trim level/ body shape)
    user selects that for engine size

    arguably the trim level, colour engine size fuel type are all loaded at the same time.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2008
    Posts
    17
    Quote Originally Posted by anno1095
    Any other suggestions? I'm short of ideas.

    To the above:
    I'm planning only to join each many-to-many with it's respective table. NOT joining all of the many-to-many among themselves.
    Why do you have so many tables? Surely a car can only be of one colour, one type etc., As far as I can see the only one to many relationship would be previous owners if there have been more than one.

Posting Permissions

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