[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# How to create join of 4 tables and with the folowing conditions/limitations

Posted on 2011-10-11
Medium Priority
187 Views
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
0
Question by:searchsanjaysharma
• 6
• 4
• 2

LVL 22

Accepted Solution

Thomasian earned 2000 total points
ID: 36953890
``````DECLARE @t1 table (pk_rollid bigint)
DECLARE @t2 table (uin bigint)
DECLARE @t3 table (uin bigint)
DECLARE @t4 table (uin bigint)

INSERT @t1
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 7

INSERT @t2
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3

INSERT @t3
SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5

INSERT @t4
SELECT 1
UNION ALL SELECT 5
UNION ALL SELECT 7

SELECT
T1=T1.pk_rollid
,T2=T2.uin
,T3=T3.uin
,T4=T4.uin
FROM
@t1 T1
FULL JOIN @t2 T2 ON T1.pk_rollid=T2.uin
FULL JOIN @t3 T3 ON T3.uin IN (T1.pk_rollid,T2.uin)
FULL JOIN @t4 T4 ON T4.uin IN (T1.pk_rollid,T2.uin,T3.uin)
``````
0

Author Comment

ID: 36953951
Please do it from permanent tables. here t4 has id as varchar field
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)

0

LVL 22

Assisted Solution

Thomasian earned 2000 total points
ID: 36953963
``````SELECT
T1=T1.pk_rollid
,T2=T2.uin
,T3=T3.uin
,T4=T4.pk_rollid
FROM
t1 T1
FULL JOIN t2 T2 ON T1.pk_rollid=T2.uin
FULL JOIN t3 T3 ON T3.uin IN (T1.pk_rollid,T2.uin)
FULL JOIN t4 T4 ON T4.pk_rollid IN (CAST(T1.pk_rollid as varchar),CAST(T2.uin as varchar),CAST(T3.uin as varchar))
``````
0

Author Comment

ID: 36954033
Thanks a lot,i want null to be replaced by 'MISSING' keyword
0

LVL 22

Assisted Solution

Thomasian earned 2000 total points
ID: 36954076
You will need to convert the numbers to varchar then apply ISNULL function
``````SELECT
T1=T1.pk_rollid
,T2=ISNULL(Cast(T2.uin as varchar), 'MISSING')
,T3=ISNULL(Cast(T3.uin as varchar), 'MISSING')
,T4=ISNULL(T4.pk_rollid, 'MISSING')
FROM
t1 T1
FULL JOIN t2 T2 ON T1.pk_rollid=T2.uin
FULL JOIN t3 T3 ON T3.uin IN (T1.pk_rollid,T2.uin)
FULL JOIN t4 T4 ON T4.pk_rollid IN (CAST(T1.pk_rollid as varchar),CAST(T2.uin as varchar),CAST(T3.uin as varchar))
``````
0

Author Comment

ID: 36954091
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
0

LVL 22

Assisted Solution

Thomasian earned 2000 total points
ID: 36954405
``````SELECT
T1=T1.pk_rollid
,T2=ISNULL(Cast(T2.uin as varchar), 'MISSING')
,T3=ISNULL(Cast(T3.uin as varchar), 'MISSING')
,T4=ISNULL(T4.pk_rollid, 'MISSING')
FROM
t1 T1
FULL JOIN t2 T2 ON T1.pk_rollid=T2.uin AND T2.finishdate BETWEEN '1 Jul 2010' and '30 Sep 2010'
FULL JOIN t3 T3 ON T3.uin IN (T1.pk_rollid,T2.uin) AND T3.finishdate BETWEEN '1 Jul 2010' and '30 Sep 2010'
FULL JOIN t4 T4 ON T4.pk_rollid IN (CAST(T1.pk_rollid as varchar),CAST(T2.uin as varchar),CAST(T3.uin as varchar)) AND T4.finishdate BETWEEN '1 Jul 2010' and '30 Sep 2010'
WHERE
T1.finishdate BETWEEN '1 Jul 2010' and '30 Sep 2010'
AND T1.name is not null
``````
0

LVL 70

Expert Comment

ID: 36971043
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
0

LVL 22

Assisted Solution

Thomasian earned 2000 total points
ID: 36972007
>>You need LEFT JOIN here rather than FULL JOIN.
I used full join under the impression that the ids could also be missing from t1, but forgot about it when adding the conditions on the follow-up requirements.

Upon rereading the comments, it seems that t1 will have the complete list of ids so you are probably right that LEFT JOIN will do.

>>--btw, you don't need to check for t1.pk_rollid is not null, the left joins take care of that
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.
0

LVL 70

Expert Comment

ID: 36979420
>> 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.

0

LVL 22

Assisted Solution

Thomasian earned 2000 total points
ID: 36979937
From question: "The values which are generated in t1, are entered in t2,t3,t4 and then inserted there."

From what I understand (upon rereading the question), table t1 should contain a complete list of the ids which is why I agreed that LEFT JOIN should be used instead of FULL JOIN.

If that is not the case, then FULL JOIN should be used to show the "missing" ids in t1.
0

Author Closing Comment

ID: 37060804
Good
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed ā¦
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiā¦
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculā¦
###### Suggested Courses
Course of the Month17 days, 20 hours left to enroll