Clever Geek Handbook

Co-local parent and child table in SQL statement to get records of both tables

I have two tables: one parent table

ID Name
1  Sam
2  Ricky

Then I have a child table, here we have a relationship for one to many, like a sam record has multiple addresses

How (table for children)

ID Parent_ID Address
1  1         Newyork
2  1         Chicago

Now what I want to do is write a query where I will get data from both tables like

ID Name Address1 Address2
1  Sam  Newyork  Chicago

I know what is the maximum lack of address. Even some data has the same address. in this case Address2 will be empty or empty.

Thanks for the help!

+3


source to share


3 answers


Using INNER JOIN

and PIVOT

u will give you the result. Try it.

CREATE TABLE #par
  (
     ID   INT,
     Name VARCHAR(100)
  )

INSERT #par
VALUES (1,'Sam'),
       (2,'Ricky')

CREATE TABLE #chil
  (
     ID        INT,
     Parent_ID INT,
     Addresss  VARCHAR(100)
  )

INSERT #chil
VALUES( 1,1,'Newyork'),
       (2,1,'Chicago')


SET @col=(SELECT ',[' + Cast(Addresss AS VARCHAR(100)) + ']'
          FROM   #chil
          FOR XML PATH(''))

SELECT @col = RIGHT(@col, Len(@col) - 1)

SET @col1=(SELECT ',[' + Cast(Addresss AS VARCHAR(100))
                  + '] as Address'
                  + CONVERT(VARCHAR(50), Row_number() OVER (ORDER BY ID))
           FROM   #chil
           FOR XML PATH(''))

SELECT @col1 = RIGHT(@col1, Len(@col1) - 1)

SET @sql= 'SELECT id,name,' + @col1
          + '
    FROM   (SELECT a.id,
                   a.name,
                   b.Addresss Addresss
            FROM   #par a
                   INNER JOIN #chil b
                           ON a.id = b.Parent_ID) p
           PIVOT (Max(Addresss)
                 FOR Addresss IN(' + @col + ')) AS piv '

EXEC Sp_executesql
  @sql


OUTPUT

id  name    Address1    Address2
1   Sam      Newyork    Chicago
+2


source


there should be something like

select * from parent inner join child on child.parent_id = parent.id


but this is a simple connection. maybe I missed your question?

-1


source


the solution in mssql is the PIVOT keyword.

I'll add an example soon

-1


source






More articles:


All Articles