Access SQL query ---> SQL SelfJoin

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

regards

 

[r937]

since you already know the SN, VN, GN values (because you give them in the WHERE clause), there's really no need to put them into the SELECT clause, because doing so requires that you also use a GROUP BY clause, and since you omitted it, that was the cause of the error

so just do this --

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

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markhan]

Thanks for the reply but that doesnt fit with what i want.

I need to show in the query SN, VN, GV and LD.

If i use the query you say It will only show LD value and i need all the values.

IE.
SN VN GV LD
a a a 1
b b b 2

With your query the result it would be:
LD
1

The result i need is:
SN VN GV LD
a a a 1

I could have used this query but is not efficient:
(As it takes lots of data I dont need)

Select *
FROM log
Where SN = 'X' AND VN = 'Y' AND GV = 'Z'
ORDER BY LD DESC

And then take the first row. But as i said before is not efficient.

This is the query i need to change to Self join unless someone tell me that is less efficient a self join rather than 2 selects in one query.

This is how it looks like right now my query:

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') )));

Any help it would be appreciated.

Thanks

 

[r937]

okay, perhaps i took your posts too literally... after all, you ~did~ say

Where SN = 'X' AND VN = 'Y' AND GV = 'Z'

perhaps this is what you wanted --

SELECT SN
     , VN
     , GN
     , MIN(LD)  
  FROM l 
GROUP
    BY SN
     , VN
     , GN

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markhan]

:/ Ok... Sorry for the bad explanation.

I need to check 3 conditions and from those conditions get the minimun value.

So if i have this table:

SN VN GV LD
a a1 a2 1
a a2 a3 2
X Y Z 1
X Y Z 2
x b c 1
b b b 2
--------------

Conditions: SN = 'X' AND VN = 'Y' AND GV= 'Z' AND MIN(LD)

My query should show just this:

SN VN GV LD
X Y Z 1

Right now my query shows:
SN VN GV LD
X Y Z 1

So it works fine. But im looking to be more efficient thats why i thought in a Self join.

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') )));

I hope you understand now what i would like to do.

Thanks a lot for the help.

Regards

 

[r937]

did you try my query with the GROUP BY?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markhan]

Yep I tried the query with group by but it doesnt achieve all the conditions I need.

with group by shows all the values for the minimun LDs each.

I have 2 options:

1º Use 2 Selects (As right now)
2º Use a Self join? (Is this possible) If so is more efficient than 2 Selects?

 

[r937]

perhaps you could show the 2 selects you're using as right now

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

group by shows all the values for the minimun LDs each
So, you wanted simply this ?

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markhan]

Omg thanks a lot (( (So much time wasted ¬¬)

I didnt even need 2 selects

Where were you PHV!! i needed you before!! haha just kidding

Thanks a lot

This ends the question I just needed more group by...

 

[markhan]

r937 Thanks for the help

by the way i was showing the 2 select query i was using since the beginning ;P

 

[r937]

i don't want to sound disappointed, but PHV's solution is the same as the solution i gave you in post #2, except the constants specified in the WHERE clause are shown in the SELECT clause (and therefore have to be in the GROUP BY clause as well)

i figured that if you already knew the values 'X' and 'Y' and 'Z' you would only need the MIN, but apparently you needed to have the values that you already knew displayed back to you



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markhan]

r937 You were close to the solution but probably we misunderstood each other. :/ sorry about that.

I really appreciate all the time you spend to find the solution.
Aswell i thought the solution of my problem was harder than i expected :/

In my #1 post i wrote this query

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

And i only needed to mix your #2post with mine rather than using 2 selects :/.

kind regards