asked
Select Parent..Child records from 2 tables in one query
I have 2 tables called YL0PF (which is the Master or Parent record) and YL1PF (which is the detail or Child record). They join like this YL0AB=YL1AB, YL0AN=YL1AN, YL0AS=YL1AS and we restrict records by the column DTEINP. There can be 0 to many detail records for 1 master record. The Child records are basically arrears records showing what months payments have been missed by the month they were due So I can of course do this: SELECT Y0.* , Y1.* FROM alpsProduction..YL0PF Y0 LEFT OUTER JOIN alpsProduction..YL1PF Y1 ON Y0.L0AB = y1.L1AB AND Y0.L0AN = Y1.L1AN AND Y0.L0AS = Y1.L1AS WHERE Y0.DTEINP = '01 Jun 2015' ORDER BY Y0.L0AB + Y0.L0AN + Y0.L0AS , Y1.L1AB + Y1.L1AN + Y1.L1AS , CAST(Y1.DTEINP AS DATE) DESC and this will put the child records next to the repeating Parent records. But if possible I would like to extract the data as below: > L0AS1, L0AN1, L0AS1, * >> L1AS1, L1AN1, L1AS1, * >> L1AS1, L1AN1, L1AS1, * > L0AS2, L0AN2, L0AS2, * >> L1AS2, L1AN2, L1AS2, * >> L1AS2, L1AN2, L1AS2, * >> L1AS2, L1AN2, L1AS2, * So how could I do that?
You could use a recursive common-table-expression. See example D here :
https://msdn.microsoft.com/en-GB/library/ms175972.aspx
15 People are following this question.
