May 23, 2008 at 4:18 am
Hi All,
I am having 2nos. of Table.
1 st table contains
Id | Name
------------
100 abc
2nd table contains
Id | Name | Code
----------------------
100 abc xyz
100 abc ghi
My select statement should be from 2 tables
Id | Name | Code | Code-1
-----------------------------------
100 abc xyz ghi
it will combine the 2 table id and name and return the result.
how can i do it?
regards,
Milu
100 abc
May 23, 2008 at 5:51 am
[font="Verdana"]Follow the below URL. http://msdn.microsoft.com/en-us/library/ms177410.aspx?wt.slv=RightRail [/font]
MH-09-AM-8694
May 23, 2008 at 6:09 am
Do you need the codes as separte columns? If yes then as suggested by Mr.Mahesh and use PIVOT to accomplish the functionality. If you are looking at fetching the codes for distinct ID and Name values then you can also use this
ALTER FUNCTION GetCodes
(@ID INT,
@Name VARCHAR(100))
RETURNS VARCHAR(1000) AS
BEGIN
Declare @Codes VARCHAR(1000)
SET @codes =''
SELECT @Codes = @Codes + CASE WHEN @Codes = '' THEN '' ELSE ' | ' END + Code
FROM
Table2 T2 INNER JOIN Table1 T1 ON T1.ID = T2.ID AND T1.Name = T2.Name
WHERE
T1.ID = @ID
AND
T1.Name = @Name
RETURN(@Codes)
END
SELECT DISTINCT(ID),
Name,
dbo.GetCodes(ID,Name) AS Codes
FROM
dbo.Table1
Prasad Bhogadi
www.inforaise.com
May 23, 2008 at 7:21 am
To combine the results of two tables or more use the join clause. Lookup left and right join. Most common is the inner, which tells sql only display data when there is a match in both table.
select * from xyzTable T1
inner join xyzTable2 T2 on T1.ID=T2.ID
Where T1 in(1,2,3)
May 23, 2008 at 7:33 am
select * from xyzTable T1
inner join xyzTable2 T2 on T1.ID=T2.ID
Where T1.ID in(1,2,3)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply