CODEWITHSUNDEEP
asp.netsqlsql-serversql-server-2005
Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

Parent Child table record - Building SQL query

Here is my table and data of these tables

Table name: Code

CID Code
1   abc
2   def
3   xyz

Table Name : Details

ID  Name    CID
1     a       1
2     b       2

Resultant Table:

ID      Code    Name
1       abc     a
2       abc     Null
3       def     b
4       def     Null
5       xyz     Null
6       xyz     Null

I nned to get all record from the code table and against each code I have to get all the rows from the details table, if some code have value their need value and if not then Null

Thanks

Upvotes: 0

Views: 363

Answers (1)

Aaronaught
Aaronaught

Reputation: 122624

Sounds like you're looking for the cartesian product:

SELECT
    c.CID * d.ID AS ID,
    c.Code,
    CASE
        WHEN c.CID = d.CID THEN d.Name
        ELSE NULL
    END AS Name
FROM Code c
CROSS JOIN Details d

Although cartesian products are quite slow for larger tables... so be sure that this is what you really want.

Upvotes: 3

Related Questions