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

回答1:

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.