OR in a JOIN
-
I added it to the post with some clarification.
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
-
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.Field2Quite 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 DSELECT * FROM TableB
ID Field1 Field2
20 A B
21 C D10 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 rowLuc'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 NULLPIEBALDconsult 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 wrote:
should eliminate any duplicates.
Except it doesn't. I have added some clarification and sample data to my post.
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 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
Yes.
-
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.Field2Quite 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 DSELECT * FROM TableB
ID Field1 Field2
20 A B
21 C D10 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 rowLuc'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 NULLYour 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
-
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.Field2Quite 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 DSELECT * FROM TableB
ID Field1 Field2
20 A B
21 C D10 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 rowLuc'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 NULLHere'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 nullThe 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
-
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 nullThe 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
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 NULLI have your cases depend on the ID field, not the other fields (where null might be valid) :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
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 NULLI have your cases depend on the ID field, not the other fields (where null might be valid) :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
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
-
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
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.
-
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.
Sorry, I missed your post, yes it is the same. Good work! :)
Luc Pattyn [My Articles] Nil Volentibus Arduum