OR in a JOIN

[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

[Paul Conrad]

PIEBALDconsult wrote:

so I want 20

Do you want 20 in both rows with the Id from Table A being 10, and 12?

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

[Chris Meech]

It will remove the duplicates of the resultant set, but now that I've read your example, that is not quite what you are after. In your example once the row with ID 12 from table A matched on FieldA for the row with ID 20, you don't want to include it anymore where it might match on FieldB.

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]

Yes.

[Luc Pattyn]

Your modified message is much clearer. And this is what works for me:

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)

just 3 SELECTs, no LEFT PS: I failed to get it to work with a CTE on SQL Server... :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Jorgen Andersson]

Here's another way to do it:

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

The plan indicates that it should be faster, but that's with dummy data. I'm curious about the performance with real data.

Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

[Luc Pattyn]

Interesting. IMO it can be simplified further, as the CTE isn't really necessary, so I now have:

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

I have your cases depend on the ID field, not the other fields (where null might be valid) :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Jorgen Andersson]

I'm getting identical plans, but your version is prettier. :-)

Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions

[Bernhard Hiller]

That's actually my solution posted above - UNION vs. UNION ALL does not make a difference here, because both queries select all columns from the same table.

[Luc Pattyn]

Sorry, I missed your post, yes it is the same. Good work! :)

Luc Pattyn [My Articles] Nil Volentibus Arduum