Works like a charm. THANKS!

LocationName will an exact match.

As far as the min/max question. It is based on the SpecName and LocationName

So if the Service table has SpecName = 'A' then on the Specification...

Thanks Wayne, if I would have known cte = common table expression I would have googled it myself.

Thanks Lutz!

It works just as intended. One question, do you know where I can find some tutorials on how to use cte's? I don't exactly know how your code...

Thanks for the reply, I am out of the office until Tuesday but I will let you know how that works. The count of ServiceID 10 should be 2...

Works like a charm. Thank you!

You are correct but even with LEFT OUTER JOIN it returns the same result of having each column on a different row.

Here is my query. If I remove the full joins or use left, right I get no results.

SELECT DISTINCT o.ClientID,o.OrderID, i.ItemID, t1.1_ID,t2.2_ID

FROM Order as o

FULL JOIN Item as...