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:
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
ORDER BY t1.cdata, t2.cdata
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)
GROUP BY cdata2
you may have to change the aggragation function that evaluates the correct value to choose.