[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
12
Medium Priority
?
187 Views
Last Modified: 2012-05-12
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
Comment
Question by:searchsanjaysharma
  • 6
  • 4
  • 2
12 Comments
 
LVL 22

Accepted Solution

by:
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)

Open in new window

0
 

Author Comment

by:searchsanjaysharma
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

by:Thomasian
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))

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

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

Assisted Solution

by:Thomasian
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))

Open in new window

0
 

Author Comment

by:searchsanjaysharma
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

by:Thomasian
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	

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
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

by:Thomasian
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

by:Scott Pletcher
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

by:Thomasian
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

by:searchsanjaysharma
ID: 37060804
Good
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question