searchsanjaysharma
asked on
How to create join of 4 tables and with the folowing conditions/limitations
There are 4 tables t1,t2,t3,t4.
t1 has field pk_rollid - datatype bigint identity(1,1)
t2 has field uin - datatype bigint
t3 has field uin - datatype bigint
t4 has fileld pk_rollid - datatype varchar
Now first table t1 is field.The values which are generated in t1, are entered in t2,t3,t4 and then inserted there.
suppose the 4 tables has following values
t1 (pk_rollid) - 1,2,3,5,7
t2(uin) - 1,2,3
t3(uin) - 2,3,5
t4(uin) - 1,5, 7
The output should come as follows
t1 t2 t3 t4
1 1 Missing 1
2 2 2 Missing
3 3 3 Missing
5 Missing 5 5
7 Missing Missing 7
t1 has field pk_rollid - datatype bigint identity(1,1)
t2 has field uin - datatype bigint
t3 has field uin - datatype bigint
t4 has fileld pk_rollid - datatype varchar
Now first table t1 is field.The values which are generated in t1, are entered in t2,t3,t4 and then inserted there.
suppose the 4 tables has following values
t1 (pk_rollid) - 1,2,3,5,7
t2(uin) - 1,2,3
t3(uin) - 2,3,5
t4(uin) - 1,5, 7
The output should come as follows
t1 t2 t3 t4
1 1 Missing 1
2 2 2 Missing
3 3 3 Missing
5 Missing 5 5
7 Missing Missing 7
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot,i want null to be replaced by 'MISSING' keyword
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks and if i want to put the condition for all tables as
where finishdate>='1 Jul 2010' and finishdate<='30 Sep 2010'
and for t1 as
T1.name is not null
where finishdate>='1 Jul 2010' and finishdate<='30 Sep 2010'
and for t1 as
T1.name is not null
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need LEFT JOIN here rather than FULL JOIN.
Also, you have to be *very* careful when comparing datetimes or you will lose rows that should match :-) :
FROM t1
LEFT OUTER JOIN t1 ON
t2.uin = t1.pk_rollid AND t2.finishdate >= 1 Jul 2010' and t2.finishdate < '1 Oct 2010'
LEFT OUTER JOIN t1 ON
t3.uin = t1.pk_rollid AND t3.finishdate >= 1 Jul 2010' and t3.finishdate < '1 Oct 2010'
LEFT OUTER JOIN t1 ON
t4uin = t1.pk_rollid AND t4.finishdate >= 1 Jul 2010' and t4.finishdate < '1 Oct 2010'
WHERE
t1.finishdate >= 1 Jul 2010' and t1.finishdate < '1 Oct 2010'
--btw, you don't need to check for t1.pk_rollid is not null, the left joins take care of that
Also, you have to be *very* careful when comparing datetimes or you will lose rows that should match :-) :
FROM t1
LEFT OUTER JOIN t1 ON
t2.uin = t1.pk_rollid AND t2.finishdate >= 1 Jul 2010' and t2.finishdate < '1 Oct 2010'
LEFT OUTER JOIN t1 ON
t3.uin = t1.pk_rollid AND t3.finishdate >= 1 Jul 2010' and t3.finishdate < '1 Oct 2010'
LEFT OUTER JOIN t1 ON
t4uin = t1.pk_rollid AND t4.finishdate >= 1 Jul 2010' and t4.finishdate < '1 Oct 2010'
WHERE
t1.finishdate >= 1 Jul 2010' and t1.finishdate < '1 Oct 2010'
--btw, you don't need to check for t1.pk_rollid is not null, the left joins take care of that
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> Actually, we are checking for the "name" for null not the "id". And LEFT JOIN will not filter anything from the outer table unless explicit specified on the WHERE clause. <<
True. I reasoned that the t1.name would not normally ever be NULL, that it was the FULL joins that were causing t1 to be NULL.
If t1.name can be NULL in the original t1 table (??), then, yes, you would need the NOT NULL check against t1.name.
True. I reasoned that the t1.name would not normally ever be NULL, that it was the FULL joins that were causing t1 to be NULL.
If t1.name can be NULL in the original t1 table (??), then, yes, you would need the NOT NULL check against t1.name.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good
ASKER
please find the script for the data
create table t1 (pk_rollid bigint identity(1,1),name varchar(200))
insert into t1(name) values('a')
insert into t1(name) values('b')
insert into t1(name) values('c')
insert into t1(name) values('d')
insert into t1(name) values('e')
create table t2 (uin bigint)
insert into t2 values(1)
insert into t2 values(2)
insert into t2 values(5)
create table t3 (uin bigint)
insert into t3 values(2)
insert into t3 values(4)
create table t4 (pk_rollid varchar(100))
insert into t4 values(1)
insert into t4 values(2)