ACCESS SQL and Self join

[markhan]

Hi all!

I was wondering if someone could help me a little with this query.
My intention is to use a Self join but i'm not transforming the query properly.

Database: Access 2000

My actual query is like this:

SELECT l.SN, l.VN, l.GN, l.LD
FROM l
WHERE (((l.SN)='X') AND ((l.VN)='Y') AND ((l.GN)='Z') AND ((l.LD)<=(Select MIN(l2.LD)  from l l2 where ((l2.SN)='X') AND ((l2.VN)='Y') AND ((l2.GN)='Z') )));

This works fine but I would like to transform it to a self join.

In case it helps what i would like to do in the query is Obtaining the minimun value from LD and show SN, VN, GN, LD for that value

This last query is what i would like to do but is a wrong query as Access shows error while trying to use it.

SELECT l.SN, l.VN, l.GN, MIN(l.LD)
FROM l
WHERE l.SN = 'X' AND l.VN = 'Y' AND l.GN = 'Z'.

Thanks in advance for the help and sorry if i didnt explain it correctly.

Any advice about the query?

regards

[GolferGuy]

Basically you need 2 queries. The first one will find the low value for LD. then you use the results of that query (the one record with the LD low value) to be the criteria that will select the record that has the same LD value as this low value. At which point you will have the original record identified with this low value for LD. then you can know what the other values are from that record.
I have never known of any other way to get those other values, than to run two queries.

[markhan]

Ok thanks, i thought that with a self join it would be possible to achieve what i wanted to do.

So i will keep the query as it is.

SELECT l.SN, l.VN, l.GN, l.LD
FROM l
WHERE (((l.SN)='X') AND ((l.VN)='Y') AND ((l.GN)='Z') 
AND ((l.LD)<=(Select MIN(l2.LD) from l l2 
where ((l2.SN)='X') AND ((l2.VN)='Y') 
AND ((l2.GN)='Z') )));

[markhan]

FIXED, SOLUTION: 1 SELECT And some group by ¬¬ sigh lol.
so simple and i couldnt see it

You dont really need 2 Selects.

[GolferGuy]

If you do a group by to get the lowest value in a field, the other fields that you have selected, you have no guarantee that those other fields are from the same record as the lowest value in the one field. It does need the two queries to be sure you have the correct data in the other fields from the record with the lowest value in the LD field. If you really feel you have it correct with one query, please post the SQL for that query.

[markhan]

If you do a group by to get the lowest value in a field, the other fields that you have selected, you have no guarantee that those other fields are from the same record as the lowest value in the one field. It does need the two queries to be sure you have the correct data in the other fields from the record with the lowest value in the LD field. If you really feel you have it correct with one query, please post the SQL for that query.

I've been doing some testing and this query works fine.

SELECT l.SN, l.VN, l.GN, MIN(l.LD)
FROM l
WHERE l.SN = 'X' AND l.VN = 'Y' AND l.GN = 'Z'
GROUP BY l.SN, l.VN, l.GN

I've been checking if it gets the minimun changing the conditions and it works fine

At the start i thought i needed 2 selects but with this last query works (I spent too much time in this haha)

regards and thanks

[GolferGuy]

What is very important to point out here, the reason the OTHER fields are the correct fields is they are the only values that your query will allow anyway. SN MUST be "X" and VN MUST be "Y" and GN MUST be "Z" So what you are really asking for is the lowest value of LD when SN, VN, and GN are "X", "Y", and "Z". If you asked for any other data from the record that holds the lowest value for LD, this query would not give you the correct value or values. The "X", "Y", and "Z" would still be correct because they are in your selection criteria, but any other data not in the selection criteria would be wrong. You would need the 2 query approach for that case.

[markhan]

What is very important to point out here, the reason the OTHER fields are the correct fields is they are the only values that your query will allow anyway. SN MUST be "X" and VN MUST be "Y" and GN MUST be "Z" So what you are really asking for is the lowest value of LD when SN, VN, and GN are "X", "Y", and "Z". If you asked for any other data from the record that holds the lowest value for LD, this query would not give you the correct value or values. The "X", "Y", and "Z" would still be correct because they are in your selection criteria, but any other data not in the selection criteria would be wrong. You would need the 2 query approach for that case.

thanks

I understood your point .

In my case with the one select query works correctly and does what it has to do.

Thanks a lot for the interest

regards