SQL Joins

[ASB]

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.

[MrMarkyMark]

Have a look at full outer joins, or a union all.
Depending on your data volume and structure, one will, most likely, perform better than another.

[WTFH]

SELECT
table1ID,
table1Field1,
table1field2,
etc,
table2field1,
table2field2,
etc
FROM
table1
LEFT OUTER JOIN table2
ON
table1ID = table2ID
UNION ALL
SELECT
table1ID,
table1Field1,
table1field2,
etc,
table2field1,
table2field2,
etc
FROM
table2
LEFT OUTER JOIN table1
ON
table1ID = table2ID




...and then remove the duplicates (or do a select around it)

[ASB]

Thanks for the pointers :-)

There is one additional piece which I forgot (sorry).

Table1 rows may want to be excluded based on a flag column in it (say "IsValid").

Reading up on UNIONS....

[WTFH]

I'm bored...



SELECT DISTINCT
One, Two, Three, Four, Five
FROM
(
SELECT
table1ID AS One,
table1Field1 AS Two,
table1field2 AS Three,
etc,
table2field1 AS Four,
table2field2 AS Five,
etc
FROM
table1
LEFT OUTER JOIN table2
ON
table1ID = table2ID
UNION ALL
SELECT
table1ID,
table1Field1,
table1field2,
etc,
table2field1,
table2field2,
etc
FROM
table2
LEFT OUTER JOIN table1
ON
table1ID = table2ID
)
Where Two <> 'Late change to spec';


(This is based on Oracle SQL. Might be slightly different on MS SQL)

[ASB]

I'm bored...

........

Bingo. Think I'm there now :-)

[ASB]

Bingo. Think I'm there now :-)

Well I was, but no.

I could get the data OK, problem I ened up with was being unable to sort it. Had all sorts of problems with the variable names being aliased. So gave up and did 2 selects into a temp table.

Follow up:-

Having got this data I am to return it in a tab delimited form eventually. I was going to do this in the .NET process which calls the SO but just wondered if there was a viable way to do it in T-SQL.

(SSIS is not an allowed to be used).

[WTFH]

I could get the data OK, problem I ened up with was being unable to sort it. Had all sorts of problems with the variable names being aliased. So gave up and did 2 selects into a temp table.

The original spec didn't mention sorting.


A contractor can only deliver based on the spec given!


Are you marketing yourself as a project manager with Prince2 and an MBA? :lol:

[MrMarkyMark]

The original spec didn't mention sorting.


A contractor can only deliver based on the spec given!


Are you marketing yourself as a project manager with Prince2 and an MBA? :lol:

If he was, do you think he would be getting his hands dirty with this

[ASB]

The original spec didn't mention sorting.


A contractor can only deliver based on the spec given!


Are you marketing yourself as a project manager with Prince2 and an MBA? :lol:

Very true. The partial spec given was met entirely.

The lack of completeness was because I was looking for pointers and to learn, which you have all been very helpful with.

In my defence it is was far more comprehensive than my spec. Which is "here have some data, import it, reconcile it and export it".

The first meeting about the issues is currently scheduled for 2 weeks after it goes into pilot. What could possibly go wrong.

Still I can add sql non-expert to the CV.