SQL Query Group by help!

[shauntck]

Hey guys, i've got a table zeqld(key, equipment, salesord, material). There are duplicate equipments and salesords. I need to do what seems to be a simple thing although I'm not familiar with how exactly do it.

I need to get all equipments that have more then 1 salesord.

In essence, here is an example scenario

equipment order material
1 A mat1
1 A mat2
2 B mat3
2 B mat4
2 C mat5
3 D mat6
3 E mat7
4 F mat8
4 F mat9

I would want to see
equipment 2 because order B and C are different and part of equipment 2 AND equipment 3 because sales orders D and E are different.
I'm pretty sure i need a group by clause although its just not coming together. Any help would be greatly appreciated.

Thanks much,
-Shaunt

 

[PHV]

Something like this ?
SELECT equipment
FROM zeqld
GROUP BY equipment
HAVING Min(salesord)<>Max(salesord)
;

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

 

[shauntck]

yep thats exactly right! Thanks a lot.

Now I'm trying to get the distinct equipments and salesords to display also.

i was thinking this:
SELECT distinct a.equipment, b.salesord
FROM zeqld where a.equipment in
(SELECT b.equipment
FROM zeqld b
GROUP BY equipment
HAVING Min(b.salesord)<>Max(b.salesord));

but for some reason that is crashing my computer... is something wrong with that?

 

[PHV]

Either try this:
SELECT distinct equipment, salesord
FROM zeqld where equipment in
(SELECT equipment
FROM zeqld
GROUP BY equipment
HAVING Min(salesord)<>Max(salesord));
Or this:
SELECT DISTINCT A.equipment, salesord
FROM zeqld A INNER JOIN
(SELECT equipment FROM zeqld GROUP BY equipment
HAVING Min(salesord)<>Max(salesord)
) B ON A.equipment=B.equipment;

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

 

[shauntck]

Thanks man, you are very good at your SQL =)

-Shaunt