Click here to Skip to main content
15,851,242 members
Home / Discussions / Database
   

Database

 
AnswerRe: function Pin
kmoorevs14-Jun-12 6:08
kmoorevs14-Jun-12 6:08 
QuestionHow to send an invitation mail in c# to a client Pin
rp7867-Jun-12 19:43
rp7867-Jun-12 19:43 
AnswerRe: How to send an invitation mail in c# to a client Pin
Paul Conrad7-Jun-12 19:49
professionalPaul Conrad7-Jun-12 19:49 
AnswerRe: How to send an invitation mail in c# to a client Pin
Sandeep Mewara7-Jun-12 21:04
mveSandeep Mewara7-Jun-12 21:04 
AnswerRe: How to send an invitation mail in c# to a client Pin
vvashishta10-Jun-12 20:11
vvashishta10-Jun-12 20:11 
QuestionSQL Server CLR Parameter Types Pin
cjb1106-Jun-12 5:32
cjb1106-Jun-12 5:32 
AnswerRe: SQL Server CLR Parameter Types Pin
PIEBALDconsult6-Jun-12 5:58
mvePIEBALDconsult6-Jun-12 5:58 
QuestionOR in a JOIN Pin
PIEBALDconsult5-Jun-12 14:51
mvePIEBALDconsult5-Jun-12 14:51 
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


modified 6-Jun-12 16:38pm.

AnswerRe: OR in a JOIN Pin
Paul Conrad5-Jun-12 15:03
professionalPaul Conrad5-Jun-12 15:03 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult5-Jun-12 15:26
mvePIEBALDconsult5-Jun-12 15:26 
AnswerRe: OR in a JOIN Pin
Luc Pattyn5-Jun-12 17:27
sitebuilderLuc Pattyn5-Jun-12 17:27 
GeneralRe: OR in a JOIN Pin
Paul Conrad5-Jun-12 18:00
professionalPaul Conrad5-Jun-12 18:00 
AnswerRe: OR in a JOIN Pin
Luc Pattyn5-Jun-12 18:31
sitebuilderLuc Pattyn5-Jun-12 18:31 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult5-Jun-12 19:10
mvePIEBALDconsult5-Jun-12 19:10 
GeneralRe: OR in a JOIN Pin
Paul Conrad5-Jun-12 20:15
professionalPaul Conrad5-Jun-12 20:15 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 4:46
mvePIEBALDconsult6-Jun-12 4:46 
GeneralRe: OR in a JOIN Pin
Paul Conrad6-Jun-12 8:38
professionalPaul Conrad6-Jun-12 8:38 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 10:12
mvePIEBALDconsult6-Jun-12 10:12 
GeneralRe: OR in a JOIN Pin
Paul Conrad6-Jun-12 10:16
professionalPaul Conrad6-Jun-12 10:16 
AnswerRe: OR in a JOIN Pin
pmpdesign5-Jun-12 20:34
pmpdesign5-Jun-12 20:34 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 4:24
mvePIEBALDconsult6-Jun-12 4:24 
AnswerRe: OR in a JOIN Pin
Bernhard Hiller5-Jun-12 22:09
Bernhard Hiller5-Jun-12 22:09 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 4:26
mvePIEBALDconsult6-Jun-12 4:26 
AnswerRe: OR in a JOIN Pin
Chris Meech6-Jun-12 5:30
Chris Meech6-Jun-12 5:30 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 6:55
mvePIEBALDconsult6-Jun-12 6:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.