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 NULLIs 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
-
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
I don't know whether or not such a stored procedure could be devised, but if so, it could be executed.
-
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