Category: Smart Devices

27 Dec 2012 22:18

Joined:	15/09/2006
Level:	Gold
Points:	342

Points:	5

Join Problem between tables

I have a 3 result sets from 3 temp tables

I want to join these 3 tables get the common column id in single column

For Eg:

StudID from table1
1
null
3

StudID name from table2
2 C

StudID name from table3
1 A
4 B
5 C

Select a.studid,name from table1 a,table2 b,table3 c where a.StudID = b.StudID
and b.StudID =c.StudID and c.StudID =a.StudID

I am getting the result set like
1
null
3

But I want the result like
1
2
3
4
5
in student id column

How to get this ressult set

Thanks in advance

#699202

27 Dec 2012 22:37

Joined:	23/12/2009
Level:	Gold
Points:	3113

Points:	3

if you have all different student id in different table like as in your sample id 2 is in table2 not in table1 and table3 than you don't use join because that cause problem you should use UNION

Select StudId From table1
Union
Select StudId From table2
Union
Select StudId From table3

this gives you result set as you like if data is same as you given in sample than
StudId
1
2
3
4
5

Regards & thanks
Arvind kumar
Visit--blog.akumars.esoftera.in

#699213

27 Dec 2012 23:15

Joined:	08/10/2009
Level:	Gold
Points:	14808

Points:	4

Rupa,

Please check this sample code


CREATE TABLE #Tbl1(StudID INT)
GO
INSERT INTO #Tbl1(StudID)
VALUES(1)
INSERT INTO #Tbl1(StudID)
VALUES(null)
INSERT INTO #Tbl1(StudID)
VALUES(3)
GO
CREATE TABLE #Tbl2(StudID INT, Name VARCHAR(50))
GO
INSERT INTO #Tbl2(StudID, Name)
VALUES(2, 'C')
GO
CREATE TABLE #Tbl3(StudID INT, Name VARCHAR(50))
GO
INSERT INTO #Tbl3(StudID, Name)
VALUES(1, 'A')
INSERT INTO #Tbl3(StudID, Name)
VALUES(4, 'B')
INSERT INTO #Tbl3(StudID, Name)
VALUES(5, 'C')
GO

-- Query #1

WITH ResultSet1(StudID, Name)
AS
(
	SELECT StudID, NULL
	FROM #Tbl1	
	WHERE StudID IS NOT NULL
)
, ResultSet2(StudID, Name)
AS
(
	SELECT StudID, Name
	FROM #Tbl2
	WHERE StudID IS NOT NULL	
)
, ResultSet3(StudID, Name)
AS
(
	SELECT StudID, Name
	FROM #Tbl3
	WHERE StudID IS NOT NULL
	UNION 
	SELECT StudID, Name
	FROM ResultSet2
	UNION 
	SELECT StudID, Name
	FROM ResultSet1
)
SELECT StudID, MAX(Name) AS Name FROM ResultSet3
GROUP BY StudID



DROP TABLE #Tbl1
DROP TABLE #Tbl2
DROP TABLE #Tbl3

Please mark this as Answer, if this helps

Regards,
Alwyn Duraisingh.M
<< Database Administrator >>
Jesus saves! The rest of us better make backups...

#701757

21 Jan 2013 06:10

Joined:	27/12/2012
Level:	Gold
Points:	864

Points:	4

Hi,

Try this..

CREATE TABLE table1(StudID INT)
GO
INSERT INTO table1(StudID)VALUES(1)
INSERT INTO table1(StudID)VALUES(null)
INSERT INTO table1(StudID)VALUES(3)
GO

CREATE TABLE table2(StudID INT, Name VARCHAR(50))
GO
INSERT INTO table2(StudID, Name)VALUES(2, 'C')
GO

CREATE TABLE table3(StudID INT, Name VARCHAR(50))
GO

INSERT INTO table3(StudID, Name)VALUES(1, 'A')
INSERT INTO table3(StudID, Name)VALUES(4, 'B')
INSERT INTO table3(StudID, Name)VALUES(5, 'C')
GO

;With Result AS
(
select studid from table1 where StudID is not null
union all
select studid from table2 where StudID is not null
union all
select studid from table3 where StudID is not null
)
select distinct * from Result order by StudID asc

Join Problem between tables

About dotnetspider.com

Quick Links

General

Services