erikTsomik
asked on
Groping output using coldfusion and sql
I have the query that returns a number of records. I need to output the result into html table in such that it shows all parents records and children underneath .
the only thing the parent is identified as 0 and associated children with ID
Here is the data output. Where locHubID = 0 is a parent
the only thing the parent is identified as 0 and associated children with ID
SELECT DISTINCT L.locationKey,L.lochubid
from location L
WHERE 1 = 1
ORDER BY L.locHubID,L.locationKey
Here is the data output. Where locHubID = 0 is a parent
185 0
225 0
171 185
205 185
252 185
1 225
2 225
3 225
4 225
5 225
6 225
7 225
8 225
9 225
10 225
11 225
12 225
13 225
14 225
15 225
16 225
17 225
18 225
19 225
20 225
21 225
22 225
23 225
24 225
25 225
26 225
27 225
28 225
30 225
31 225
32 225
34 225
35 225
36 225
37 225
38 225
39 225
40 225
41 225
42 225
43 225
44 225
45 225
46 225
47 225
48 225
49 225
50 225
51 225
52 225
53 225
54 225
55 225
56 225
57 225
58 225
60 225
61 225
62 225
63 225
64 225
65 225
66 225
67 225
68 225
70 225
71 225
72 225
73 225
74 225
75 225
76 225
77 225
78 225
79 225
80 225
81 225
82 225
83 225
84 225
85 225
86 225
88 225
89 225
90 225
91 225
92 225
93 225
94 225
95 225
96 225
97 225
98 225
99 225
100 225
101 225
102 225
103 225
104 225
105 225
106 225
107 225
108 225
109 225
110 225
111 225
112 225
113 225
114 225
115 225
117 225
118 225
119 225
120 225
121 225
122 225
123 225
124 225
125 225
126 225
127 225
128 225
129 225
130 225
131 225
132 225
133 225
135 225
136 225
137 225
138 225
139 225
140 225
141 225
142 225
143 225
144 225
145 225
146 225
147 225
148 225
149 225
150 225
151 225
152 225
153 225
154 225
156 225
157 225
158 225
159 225
160 225
161 225
162 225
163 225
164 225
165 225
166 225
167 225
168 225
169 225
170 225
172 225
173 225
174 225
175 225
176 225
177 225
178 225
179 225
180 225
181 225
182 225
183 225
184 225
186 225
187 225
188 225
189 225
190 225
191 225
192 225
193 225
194 225
195 225
196 225
197 225
198 225
199 225
200 225
201 225
202 225
203 225
204 225
206 225
207 225
208 225
209 225
210 225
211 225
212 225
213 225
214 225
215 225
216 225
217 225
218 225
219 225
220 225
221 225
222 225
223 225
224 225
226 225
227 225
228 225
229 225
230 225
231 225
232 225
233 225
234 225
235 225
236 225
237 225
238 225
239 225
240 225
241 225
242 225
243 225
244 225
245 225
246 225
247 225
248 225
249 225
250 225
251 225
253 225
254 225
255 225
256 225
257 225
258 225
259 225
Hi, the question is not entirely clear, as you haven't titled the columns of your sample data. I would suggest you need to SELF JOIN the table as shown below:
Which produces results as shown below:
select l1.lochubId ,l1.id,l2.id as ID2
from location l1 inner join location l2 on l2.lochubid=l1.id
where l1.lochubid=0
order by l1.lochubid,l1.id,l2.id;
Which produces results as shown below:
LOCHUBID ID ID2
0 185 171
0 185 205
0 185 252
0 225 1
0 225 2
0 225 3
0 225 4
Can you explain where do you need help?
In SQL or HTML?
In SQL or HTML?
ASKER
the help needed in sql. By simply joining to itself I only get records where locHubID = 0 I alaso need all records
Please send a desired output based on the example you posted.
+1 on Vitor's comment: "Please send a desired output based on the example you posted. ".
The self join I offered as a suggestion was based on joining parent to child records. It could be modified to show orphan child records as well. We do need to see the column titles and desired output based on a sample that includes all the scenarios you need to cater for (e.g. parents with AND without children, orphaned child records etc).
The self join I offered as a suggestion was based on joining parent to child records. It could be modified to show orphan child records as well. We do need to see the column titles and desired output based on a sample that includes all the scenarios you need to cater for (e.g. parents with AND without children, orphaned child records etc).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Erik, I appreciate the points but since my answer was built on Simon's query, it should probably be a split :)
ASKER