Join Returns too many rows

Hi

I'm sure this is a real noob question and it may be something I have know the answer to in the past but I can't remember and its been driving me mad for hours. If anyone can tell me how to do this it would make my day!

I have simplified the problem for the purpose of clarity and have attached a sript to create a simple example table.

the table looks like this:

id cMatch cData
1 A A1
2 B B1
3 C C1
4 B B2
5 A A2
6 B B3

I want to be able to do a join on the two table that only returns the following:

t1.cData t2.cData
A1 A2
B1 B2
B1 B3

The Closest I can get is with the following qry:

SELECT t1.cdata, t2.cdata from tmp_Table1 t1
JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch AND t1.cdata<>t2.cdata
WHERE t1.cdata<t2.cdata
ORDER BY t1.cdata, t2.cdata

Which returns:

t1.cData t2.cData
A1 A2
B1 B2
B1 B3
B2 B3Not sure if I attached the script last time so I thought I'd make sure.

thanks in advance for all your help!

Andy|||Hi

while not knowing your specific database, this will work with the example you provided:

SELECT MIN(cdata1), cdata2 FROM
(
SELECT t1.cdata AS cdata1, t2.cdata AS cdata2 FROM tmp_Table1 t1
INNER JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch
AND t1.cData <> t2.cData
AND t1.id < t2.id)
AS subtable
GROUP BY cdata2

you may have to change the aggragation function that evaluates the correct value to choose.