Distinct result based on only 1 field column

[NotesUser]

I have 2 tables:
- TableOne has fields A,B,C,D,E
- TableTwo has fields B,F,G,H,I,J

User will provide input parameter '@InputParameter' (i.e. field A in TableOne) into TableOne, then need to lookup the B value in TableOne.

With the B value found in TableOne, need to display TableTwo fields with this B value, but only with unique F values

What i have below will return duplicates, but i only want distinct result based on column F in TableTwo. Do i need 2 select statements???

SELECT [TableOne].[A], [TableTwo.*] FROM [TableOne], [TableTwo] WHERE
(([TableOne]. = [TableTwo].)
AND ([TableOne].[A] = @InputParameter))

 

[juliane26]

Well, you need a grouping condition then ...
what should happen to G, H and I ? sum or min or max ?
You have to give a condition to make the selection for F to be unique.
The SQL could look like the following e.g. for minimum of G and H:

select T1.A, T2.B, T2.F, min(T2.G), min(T2.H)
from T1, T2
where T1.B = T2.B
and T1.A = ParameterValue
group by T1.A, T2.B, T2.F




Juliane

 

[PHV]

Something like this ?
SELECT A.A, B.B, B.F, Max(B.G) AS SomeG, Max(B.H) AS SomeH, Max(B.I) AS SomeI, Max(B.J) AS SomeJ
FROM TableOne A INNER JOIN TableTwo B ON A.B = B.B
WHERE A.A = @InputParameter
GROUP BY A.A, B.B, B.F

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886