SQL problem

[sasom88]

Hi!

I need some help. Let's say I have 3 tables (t1(id,ds), t2(id,ds) and t3(id,ds)) where the tables don't necessarily have the same id fields.
For example:
t1
id ds
1 1
2 0
5 7

t2
id ds
8 5
1 2

t3
id ds
9 3
8 5
5 9

I need sql select statement of which resault would be:

id ds1 ds2 ds3
1__1__2__""
5__7__""__9

So I would like to have a list of id (+ds1,ds2,ds3 - all of them, if the id doesn't exsist in one of the table, there shold be "") where one of the following is true: ds(t1)<>ds(t2) or ds(t2)<>ds(t3) or ds(t1)<>ds(t3).

If the id would be the same in all three tables, i would do this:

select t1.id, t1.ds, t2.ds, t3.ds
from t1, t2, t3
where t1.ds<>t2.ds or t1.ds<>t3.ds or t2.ds<>t3.ds

But i don't know what to do in my case, where tables don't necessarily have the same id fields.

I am pretty new in sql, so any help would be appreciated. Thank you.

[moone009]

If the tables to do not all have an identifier that join each other you are out of luck.

Try this:

select *
from t1 a
full outer join t2 b on a.id = b.id
full outer join t3 c on b.id = c.id

[BitBlt]

Interesting and subtle little problem. But your summary of the requirements doesn't quite match the result set you display.

Let me see if I understand the requirements based on the result set displayed:
1. If there is a match in ID between t1 and t2 only, show the row.
2. If there is a match in ID between t1 and t3 only, show the row.
3. If there is NO match in ID between t1 and either t2 OR t3, do NOT show the row
4. If there is a match between t1 and BOTH t2 AND t3, show the row.

If that is the case, then this statement should do what you want.

select t1.id, t1.ds, t2.ds, t3.ds 
from t1
left join t2
on t1.id = t2.id
left join t3
on t1.id = t3.id
where 
(t2.id is null
or t3.id is null)
and not(t2.id is null and t3.id is null)

I tested this in SQL2008, and it seemed to work fine. The only question I had was whether or not assumption 4 above was correct. There was not enough test data to determine, so I just made a "command decision" to include it. If assumption 4 is not correct, then the first part of the "WHERE" clause would have to be broken like so:

select t1.id, t1.ds, t2.ds, t3.ds 
from t1
left join t2
on t1.id = t2.id
left join t3
on t1.id = t3.id
where 
(
  (t2.id is null and t3.id is not null)
  or
  (t2.id is not null and t3.id is null)
)
and not(t2.id is null and t3.id is null)

I'm going to give a whack at coding up something based on your summary, and the test data. In the mean time, I hope these examples help. Good luck!