January 6, 2005 at 3:55 am
Hi,
Kindly see the following scenario. How can we get the Required output?
Thanks in advance.
Unnic
-----------------------------------------
CREATE TABLE [dbo].[Table3] (
[Site] [nvarchar] (50)
[IPAddress] [nvarchar] (50)
)
GO
CREATE TABLE [dbo].[Table4] (
[Site] [nvarchar] (50)
[HostName] [nvarchar] (50)
)
GO
INSERT INTO Table3 (Site,IPAddress) VALUES ('Site1','1.1.1.1' )
GO
INSERT INTO Table3 (Site,IPAddress) VALUES ('Site2','2.2.2.2' )
GO
INSERT INTO Table4 (Site,HostName) VALUES ('Site1','Host1' )
GO
INSERT INTO Table4 (Site,HostName) VALUES ('Site3','Host3' )
The query we run:
--------------------
SELECT * FROM Table3 FULL OUTER JOIN Table4 ON Table3.Site = Table4.Site
The output we got :
-------------------
Site IPAddress Site HostName
------------------------------------------------
Site1 1.1.1.1 Site1 Host1
NULL NULL Site3 Host1
Site2 2.2.2.2 NULL NULL
Required output is:
--------------------
SiteIPAddressHostName
--------------------------------------
Site11.1.1.1Host1
Site22.2.2.2null
Site3nullHost3
What is SQL Query to get the above Required output?
January 6, 2005 at 5:36 am
Not my best coding and I'm sure there is a better way but
SELECT T3.Site, T3.IPAddress, T4.HostName
FROM Table3 T3
LEFT JOIN Table4 T4 ON T3.Site = T4.Site
UNION
SELECT T4.Site, T3.IPAddress, T4.HostName
FROM Table3 T3
RIGHT JOIN Table4 T4 ON T3.Site = T4.Site
Good Hunting!
AJ Ahrens
webmaster@kritter.net
January 6, 2005 at 6:22 am
Or try
SELECT
IsNull(Table3.Site,Table4.Site) Site,
Table3.IPAddress,
Table4.HostName
FROM
dbo.Table3 Table3
FULL OUTER JOIN
dbo.Table4 Table4
ON
Table3.Site = Table4.Site
January 7, 2005 at 11:34 am
Try this
SELECT
site=coalesce(T3.site,T4.site),
IPAddress,
HostName
FROM
Table3 T3 FULL OUTER JOIN
Table4 T4 ON T3.Site = T4.Site
order by site
--Leonid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply