Union query sorting within sorting 1

[TheInsider]

Hi,
I have a parent and child table as follows:

Table P:

PID     N
---    ---
 1      A
 2      D
 3      B
 4      C

Table C:

CID     PID     N
---     ---    ---
 1       1      a1
 2       1      a2
 3       2      d1
 4       2      d2
 5       3      b1
 6       3      b2
 7       4      c1
 8       4      c2
 9       1      a3
10       4      c3

Is it possible to create a Union Query, or any type of query, that would provide the following results:

 N     CID
---    ---
 A      
 a1     1
 a2     2
 a3     9
 B      
 b1     5
 b2     6
 C      
 c1     7
 c2     8
 c3    10
 D
 d1     3
 d2     4

I have tried everything, but I can't figure out how to sort the child N's within the parent N's. I don't think that it is even possible.
Thanks

 

[tlbroadbent]

Try this.

Select CN As N, CID From
(Select p.N As PN,
ltrim(CID)As CID,
c.N As CN
From TableC c Inner Join TableP p
On c.PID=p.PID
Union
Select N, '', N
From TableP) As qry Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[TheInsider]

Thanks Terry! It works good but I still can't get it to sort the child N's ascending yet. I don't have SQL Server available right now, so I translated it into Access 2000 as:

Select CN As N, CID1 From
(Select p.N As PN,
ltrim(CID) As CID1,
c.N As CN
From C Inner Join P
On c.PID=p.PID
Union
Select N, '', N
From P) As qry

Results:

N	CID1
---     ----
A	
a1	1
a2	2
a3	9
A2	
a22	11      <
a21	12      <
D	
d3	10      <
d1	7       <
d2	8       <
XB	
b1	3
b2	4
XC	
c1	5
c2	6

I'm going to tinker away at it, but if anyone can give me the syntax to sort the children, that would be great.

 

[TheInsider]

Sorry I should have mentioned that I changed the data in the tables slightly for testing purposes to:

Table P:

PID	N
---    ---
1	A
2	xB
3	xC
4	D
5	A2

Table C:
CID	PID	N
---     ---    ---
1	1	a1
2	1	a2
3	2	b1
4	2	b2
5	3	c1
6	3	c2
7	4	d1
8	4	d2
9	1	a3
10	4	d3
11	5	a22
12	5	a21

 

[TheInsider]

did it!!!!!!!! here is the query if anyone runs into a similar situation in the future:

SELECT Q.N, Q.CID
FROM ((
SELECT NULL AS CID, PID, N FROM P
UNION
SELECT CID, PID, N FROM C
) AS Q LEFT JOIN P ON Q.PID = P.PID) LEFT JOIN C ON Q.CID = C.CID
ORDER BY P.N, C.N;