OR in a JOIN
-
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 NULLAh, 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
-
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
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
-
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
Thanks. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
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
: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.
-
: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.
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
-
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 NULL -
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 NULLA 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.
-
No, that won't work -- the two resultant rows are distinct.
-
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.
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:
-
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
I'll cobble something up.
-
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 NULLTo 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]
-
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]
I think that's basically what Luc suggested -- and it doesn't work.
-
I think that's basically what Luc suggested -- and it doesn't work.
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]
-
I'll cobble something up.
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
-
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]
Chris Meech wrote:
should eliminate any duplicates.
Except it doesn't. I have added some clarification and sample data to my post.
-
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
I added it to the post with some clarification.
-
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.