I need a single query to get my result.

  • 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

  • [font="Verdana"]Follow the below URL. http://msdn.microsoft.com/en-us/library/ms177410.aspx?wt.slv=RightRail [/font]

    MH-09-AM-8694

  • 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

  • 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)

  • 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