Click here to Skip to main content
15,849,571 members
Home / Discussions / Database
   

Database

 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 10:10
mvePIEBALDconsult6-Jun-12 10:10 
GeneralRe: OR in a JOIN Pin
Chris Meech6-Jun-12 11:07
Chris Meech6-Jun-12 11:07 
AnswerRe: OR in a JOIN Pin
Paul Conrad6-Jun-12 10:43
professionalPaul Conrad6-Jun-12 10:43 
GeneralRe: OR in a JOIN Pin
PIEBALDconsult6-Jun-12 11:38
mvePIEBALDconsult6-Jun-12 11:38 
AnswerRe: OR in a JOIN Pin
Luc Pattyn7-Jun-12 5:33
sitebuilderLuc Pattyn7-Jun-12 5:33 
GeneralRe: OR in a JOIN Pin
Bernhard Hiller7-Jun-12 23:47
Bernhard Hiller7-Jun-12 23:47 
GeneralRe: OR in a JOIN Pin
Luc Pattyn8-Jun-12 3:22
sitebuilderLuc Pattyn8-Jun-12 3:22 
AnswerRe: OR in a JOIN Pin
Jörgen Andersson7-Jun-12 7:51
professionalJörgen Andersson7-Jun-12 7:51 
Here's another way to do it:
SQL
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

AnswerRe: OR in a JOIN Pin
Luc Pattyn7-Jun-12 8:23
sitebuilderLuc Pattyn7-Jun-12 8:23 
GeneralRe: OR in a JOIN Pin
Jörgen Andersson7-Jun-12 9:13
professionalJörgen Andersson7-Jun-12 9:13 
QuestionTransposing SQL Record Pin
Frank Lepkowski4-Jun-12 9:22
Frank Lepkowski4-Jun-12 9:22 
AnswerRe: Transposing SQL Record Pin
David Mujica4-Jun-12 10:56
David Mujica4-Jun-12 10:56 
GeneralRe: Transposing SQL Record Pin
FrankLepkowski4-Jun-12 12:04
FrankLepkowski4-Jun-12 12:04 
AnswerRe: Transposing SQL Record Pin
Jörgen Andersson4-Jun-12 12:22
professionalJörgen Andersson4-Jun-12 12:22 
AnswerRe: Transposing SQL Record Pin
Mycroft Holmes4-Jun-12 14:11
professionalMycroft Holmes4-Jun-12 14:11 
GeneralRe: Transposing SQL Record Pin
FrankLepkowski4-Jun-12 14:23
FrankLepkowski4-Jun-12 14:23 
GeneralRe: Transposing SQL Record Pin
Mycroft Holmes4-Jun-12 15:12
professionalMycroft Holmes4-Jun-12 15:12 
Questionsmall bank application Pin
mpkuena4-Jun-12 7:51
mpkuena4-Jun-12 7:51 
AnswerRe: small bank application Pin
Paul Conrad4-Jun-12 9:06
professionalPaul Conrad4-Jun-12 9:06 
AnswerRe: small bank application Pin
R. Giskard Reventlov4-Jun-12 9:43
R. Giskard Reventlov4-Jun-12 9:43 
JokeRe: small bank application Pin
Chris Meech4-Jun-12 9:58
Chris Meech4-Jun-12 9:58 
GeneralRe: small bank application Pin
thatraja6-Jun-12 9:46
professionalthatraja6-Jun-12 9:46 
AnswerRe: small bank application Pin
thatraja4-Jun-12 18:11
professionalthatraja4-Jun-12 18:11 
GeneralRe: small bank application Pin
Paul Conrad4-Jun-12 18:12
professionalPaul Conrad4-Jun-12 18:12 
GeneralRe: small bank application Pin
thatraja6-Jun-12 9:48
professionalthatraja6-Jun-12 9:48 

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.