problem with correlated subquery please help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables :

tbl1: ID,...
tbl2: ID, ID2
tbl3: ID2, value

1 links to 2 links to 3

When i use a select query i get the folowing result

ID ID2 value

1 1 2
1 2 1
1 3 7
2 1 3
3 1 4
3 2 6

now i want to use a subquery to select the ID with the ID2 where the value
is the lowest so something like this :

ID ID2

1 2
2 1
3 1

How can i do this ??
 
You could use DMin to find the lowest value, but that would also result in a
very slow query: using the database functions in a query, especially as
criteria, can lead to very poor performance.

Another (hopefully better) approach: make a totals query. Base it on your
existing query that combines the 3 tables, and group by ID, show the Min of
Value. Then create the final query that links this totals query with your
existing query. Link the ID fields together and use Value=[MinOfValue] as
the criteria. I think this should work and run fairly quickly for you.

HTH!
 
Thanks,

the totals query solution works just fine, it's still slow but acceptable

K Dales said:
You could use DMin to find the lowest value, but that would also result in a
very slow query: using the database functions in a query, especially as
criteria, can lead to very poor performance.

Another (hopefully better) approach: make a totals query. Base it on your
existing query that combines the 3 tables, and group by ID, show the Min of
Value. Then create the final query that links this totals query with your
existing query. Link the ID fields together and use Value=[MinOfValue] as
the criteria. I think this should work and run fairly quickly for you.

HTH!

Casper said:
I have 3 tables :

tbl1: ID,...
tbl2: ID, ID2
tbl3: ID2, value

1 links to 2 links to 3

When i use a select query i get the folowing result

ID ID2 value

1 1 2
1 2 1
1 3 7
2 1 3
3 1 4
3 2 6

now i want to use a subquery to select the ID with the ID2 where the value
is the lowest so something like this :

ID ID2

1 2
2 1
3 1

How can i do this ??
Click to expand...
Click to expand...
 
Back
Top