I am about as good with SQL server as I am with a rowing boat.
I have two tables:-
TABLE1.
-> Has a UNIQUE ID, say TABLE1_ID
-> Has a column containing an ID from TABLE2 say TABLE2_ID
TABLE2.
-> Has a UNIQUE ID, say TABLE2_ID
-> Has a column containing an ID from TABLE1 say TABLE1_ID
Rows may or may not be linked.
I am trying to yield a result set with all rows from both tables.
For clarity:-
all rows which have a link
all rows from table 1 which are not in table 2
all rows from table 2 which are not in table 1
So with the follow data:
TABLE1
TABLE1_ID, Something, TABLE2_ID
200,"aaaa",300
201,"bbbb",null
TABLE2
TABLE2_ID, Something, TABLE1_ID
300,"cccc",200
301,"dddd",null
I want:
T1.TABLE1_ID,T1.Something,T1.TABLE2_ID,T2.TABLE2_I D,T2.Something,T2.TABLE1_ID
200,"aaaa",300,300,"cccc",200 <In T1 and T2
201,"bbbb",null,null,null,null <In T1 but not T2
null,null,null,301,"dddd",null <In T2 but not T1
I have tried various forms of join and where which give me all sorts of interesting output. But not what I want.
Can I do this with one select ?
I could create a temporary table then populate it from 3 selects but I'd rather not.
I feel it should be easy. But I'm too stoopid to figure it out.
I have two tables:-
TABLE1.
-> Has a UNIQUE ID, say TABLE1_ID
-> Has a column containing an ID from TABLE2 say TABLE2_ID
TABLE2.
-> Has a UNIQUE ID, say TABLE2_ID
-> Has a column containing an ID from TABLE1 say TABLE1_ID
Rows may or may not be linked.
I am trying to yield a result set with all rows from both tables.
For clarity:-
all rows which have a link
all rows from table 1 which are not in table 2
all rows from table 2 which are not in table 1
So with the follow data:
TABLE1
TABLE1_ID, Something, TABLE2_ID
200,"aaaa",300
201,"bbbb",null
TABLE2
TABLE2_ID, Something, TABLE1_ID
300,"cccc",200
301,"dddd",null
I want:
T1.TABLE1_ID,T1.Something,T1.TABLE2_ID,T2.TABLE2_I D,T2.Something,T2.TABLE1_ID
200,"aaaa",300,300,"cccc",200 <In T1 and T2
201,"bbbb",null,null,null,null <In T1 but not T2
null,null,null,301,"dddd",null <In T2 but not T1
I have tried various forms of join and where which give me all sorts of interesting output. But not what I want.
Can I do this with one select ?
I could create a temporary table then populate it from 3 selects but I'd rather not.
I feel it should be easy. But I'm too stoopid to figure it out.
Comment