OR in a JOIN

PIEBALDconsult

I'm having some trouble with a JOIN that involves an OR (in SQL Server). For example:

SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
OR A.Field2=B.Field2

Quite correctly this produces two output rows for each row in TableA that matches TableB via both Field1 and Field2* -- but for this JOIN I want to output only one row when both match (preferably the result of the Field1 match, and only report the Field2 match if Field1 doesn't match). * Clarification -- when it matches two rows in TableB; one via Field1 and the other via Field2. I haven't done much searching for pointers because I don't think it's possible, however I'm posting here just in case someone here knows of a way or a simple (SQL only) work-around. Edit -- Here's an example:

SELECT * FROM TableA

ID Field1 Field2

10 A E
11 E D
12 A D

SELECT * FROM TableB

ID Field1 Field2

20 A B
21 C D

10 will match only 20 ; 11 will match only 21 ; 12 will match both 20 and 21 -- so I want 20.

ID Field1 Field2 ID Field1 Field2
10 A E 20 A B <-- I want this row
11 E D 21 C D <-- I want this row
12 A D 20 A B <-- I want this row
12 A D 21 C D <-- I don't want this row

Luc's and pmpdesign's suggestions yield the same output. Here's a variation of Bernhard's suggestion, which seems to work:

WITH cte1 AS
(
SELECT A.ID aID
, A.Field1 aField1
, A.Field2 aField2
, B.ID bID
, B.Field1 bField1
, B.Field2 bField2
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
)
, cte2 AS
(
SELECT A.ID aID
, A.Field1 aField1
, A.Field2 aField2
, B.ID bID
, B.Field1 bField1
, B.Field2 bField2
FROM TableA A
INNER JOIN TableB B
ON A.Field2=B.Field2
)
SELECT *
FROM cte1
UNION ALL
SELECT C2.*
FROM cte2 C2
LEFT OUTER JOIN cte1 C1
ON C2.aID=C1.aID
WHERE C1.aID IS NULL

Paul Conrad

Is it possible to try this through some stored procedure?

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

PIEBALDconsult

I don't know whether or not such a stored procedure could be devised, but if so, it could be executed.

Luc Pattyn

Ah, I didn't know logic operators were allowed in the ON clause, but as they are, why not just exploit them to the fullest:

SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
OR (A.Field1<>B.Field1 AND A.Field2=B.Field2)

Maybe now is the time for a :java:?

Luc Pattyn [My Articles] Nil Volentibus Arduum

Paul Conrad

Checked it out, and it seems to work :-D

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Luc Pattyn

Thanks. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

PIEBALDconsult

:doh: The :java: hadn't worked. Neither did the tequila. Trying it now... Edit: No, that doesn't work -- because there are two different rows.

Paul Conrad

Do you have a sample data set that you are using?

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

pmpdesign

Without a dataset I can't be sure, but have you tried

SELECT DISTINCT * FROM...

Bernhard Hiller

A complicated UNION:

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
)

assuming that ID is the primary key of TableA.

PIEBALDconsult

No, that won't work -- the two resultant rows are distinct.

PIEBALDconsult

Possibly, but no, no primary key. Tried it, and it seems to work. :thumbsup: It may take a while to run on the data though. :sigh:

PIEBALDconsult

I'll cobble something up.

Chris Meech

To have the duplicates removed, I think a union would do that.

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
;

Try that. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

PIEBALDconsult

I think that's basically what Luc suggested -- and it doesn't work.

Chris Meech

It's similar to what other's have suggested except that it's a union of the two result sets, which should eliminate any duplicates.

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Paul Conrad

If it is possible, that would be cool so there can be a data set to test against and see what the expected results are :)

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

PIEBALDconsult

Chris Meech wrote:

should eliminate any duplicates.

Except it doesn't. I have added some clarification and sample data to my post.

PIEBALDconsult

I added it to the post with some clarification.

Paul Conrad

Coolness! I will check it out soon and see if there's anything I can add to this thread in terms of a solution :)

""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon