how to get sql output

Posted by Santosh4u under Sql Server on 1/7/2014 | Points: 10 | Views : 638 | Status : [Member] | Replies : 3
Hi
I have two tables mentioned below.i need to to merge these two tables and get the required output

Table 1

Name Upload
Rakesh 500
Mahesh 740
Rahul 0
Sanjay 600

Table 2

Name Download
Rakesh 200
Sanjay 120
Sambit 290


Output: from the above two tables

Name Uploads Download
Rakesh 500 200
Mahesh 740 0
Rahul 0 0
Sanjay 600 120
Sambit 0 290

Note:These Name are coming from user table.

Thanks
Santosh




Responses

Posted by: Learningtorise on: 1/8/2014 [Member] Starter | Points: 25

Up
0
Down
Use Full Outer Join For this Purpose
More about Full Join -> http://www.w3schools.com/sql/sql_join_full.asp

Use IsNull to assign alternative value in case of Null.

Query should be Something Like:

SELECT Name, ISNULL(Upload, 'Not Available') AS UPLOAD, ISNULL(Download,'Not Available') AS DOWNLOAD
FROM Table 1 A
FULL OUTER JOIN Table 2 B ON A.Name = B.Name



http://hashtagakash.wordpress.com/

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: kgovindarao523-21772 on: 1/8/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,

Try Like this,

CREATE TABLE #OUTPUTTABLE
(
Name Varchar(50),
Upload varchar(50),
DownLoad Varchar(50)
)


MERGE #OutputTable  AS e
USING
(SELECT t1.Name,t1.Upload,t2.DownLoad FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Name=t2.Name)
AS f
ON e.id=f.id
WHEN MATCHED THEN
INSERT(Name, Uploads, Download )
VALUES(f.Name,f.Upload,f.DownLoad);


Select * from #OutputTable


Thank you,
Govind

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/8/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT ISNULL(t1.Name, t2.Name) Name, ISNULL(t1.Upload, 0) Upload, ISNULL(t2.Download, 0) Download
FROM Table1 t1
FULL JOIN Table2 t2
on t1.name= t2.name


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response