• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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  
0
searchsanjaysharma
Asked:
searchsanjaysharma
  • 6
  • 4
  • 2
6 Solutions
 
ThomasianCommented:
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)

Open in new window

0
 
searchsanjaysharmaAuthor Commented:
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
 
ThomasianCommented:
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))

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
searchsanjaysharmaAuthor Commented:
Thanks a lot,i want null to be replaced by 'MISSING' keyword
0
 
ThomasianCommented:
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))

Open in new window

0
 
searchsanjaysharmaAuthor Commented:
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
 
ThomasianCommented:
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	

Open in new window

0
 
Scott PletcherSenior DBACommented:
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
 
ThomasianCommented:
>>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
 
Scott PletcherSenior DBACommented:
>> 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
 
ThomasianCommented:
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
 
searchsanjaysharmaAuthor Commented:
Good
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now