Database

SELECT DISTINCT * FROM...

SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
UNION
SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field2=B.Field2
WHERE A.ID NOT IN
(
SELECT ID
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
)

SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field1=B.Field1
UNION
SELECT *
FROM TableA A 
INNER JOIN TableB B
ON A.Field1<>B.Field1 AND A.Field2=B.Field2
;

Chris Meech wrote:

should eliminate any duplicates.

PIEBALDconsult wrote:

so I want 20

SELECT * FROM TableA A INNER JOIN TableB B ON A.Field1=B.Field1
UNION ALL
SELECT * FROM TableA A INNER JOIN TableB B ON A.Field2=B.Field2
WHERE NOT A.ID IN (SELECT A.ID FROM TableA A INNER JOIN TableB B ON A.Field1=B.Field1)

WITH CTE AS
    (
    SELECT  A.ID aID
           ,A.Field1 aField1
           ,A.Field2 aField2
           ,CASE WHEN L.ID IS NULL THEN R.ID ELSE L.ID end bID
           ,CASE WHEN L.Field1 IS NULL THEN R.field1 ELSE L.Field1 END bField1
           ,CASE WHEN L.Field2 IS NULL THEN R.Field2 ELSE L.Field2 END bField2
    FROM    TableB L
    right OUTER JOIN TableA A
    ON  l.field1 = a.field1
    left OUTER JOIN TableB R
    ON a.field2 = r.field2
    )
SELECT  aID
       ,aField1
       ,aField2
       ,bID
       ,bField1
       ,bField2
FROM    CTE
WHERE   bid IS NOT null

SELECT  A.ID aID
       ,A.Field1 aField1
       ,A.Field2 aField2
       ,CASE WHEN L.ID IS NULL THEN R.ID ELSE L.ID end bID
       ,CASE WHEN L.ID IS NULL THEN R.field1 ELSE L.Field1 END bField1
       ,CASE WHEN L.ID IS NULL THEN R.Field2 ELSE L.Field2 END bField2
FROM TableB L
RIGHT OUTER JOIN TableA A ON L.Field1 = A.Field1
LEFT  OUTER JOIN TableB R ON A.Field2 = R.Field2
WHERE L.ID IS NOT NULL OR  R.ID IS NOT NULL