We help IT Professionals succeed at work.

Groping output using coldfusion and sql

228 Views
Last Modified: 2015-01-03
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  

SELECT DISTINCT L.locationKey,L.lochubid
from location L
	WHERE 1 = 1 
	ORDER BY L.locHubID,L.locationKey 

Open in new window


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

Open in new window

Comment
Watch Question

erikTsomikSystem Architect, CF programmer

Author

Commented:
Is there any advices
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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:
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;

Open in new window


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

Open in new window

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Can you explain where do you need help?
In SQL or HTML?
erikTsomikSystem Architect, CF programmer

Author

Commented:
the help needed in sql. By simply joining to itself I only get records where locHubID = 0 I alaso need all records
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Please send a desired output based on the example you posted.
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
+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).
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Hi Erik, I appreciate the points but since my answer was built on Simon's query, it should probably be a split :)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.