[anno1095]

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?

[georgev]

Two methods;

1) JOIN

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

SELECT person_id
FROM   people
WHERE  type_id IN (SELECT type_id FROM countries WHERE county_id = 1)

[anno1095]

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.

[georgev]

Using a derived table is one option

SELECT *
FROM  (
       <insert first select query>
      ) As [some_alias]
WHERE  some_field = 'some value'

[anno1095]

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?

[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

[mike_bike_kite]

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:

$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.

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 …

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

[anno1095]

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.

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.

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.

[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?

[r937]

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"

[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.

[healdem]

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.

[TonyF123]

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.