Solved

Groping output using coldfusion and sql

Posted on 2014-12-22
8
103 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

0
Comment
Question by:erikTsomik
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 19

Author Comment

by:erikTsomik
ID: 40513341
Is there any advices
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40514513
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

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40514549
Can you explain where do you need help?
In SQL or HTML?
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40514833
the help needed in sql. By simply joining to itself I only get records where locHubID = 0 I alaso need all records
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40514904
Please send a desired output based on the example you posted.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40515033
+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).
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 40515506
the help needed in sql. By simply joining to itself I only get records where locHubID = 0 I alaso need all records

As Simon says, you need to elaborate on what mean by "all records". His query should return all parents, and their children. To include parents with 0 children, use a LEFT JOIN instead of INNER JOIN.  

For example, with this data:

Sample Data
locHubID | locationKey
0               |  185
0               |  225
0               |  999
185            | 171
185            | 205
185            | 252
225            | 1
225            | 2

Open in new window



You could use Simon's query and order the results by parent and child:

<cfquery name="getData" datasource="MySQL">
SELECT p.LocHubID
       , p.locationKey AS ParentLocationKey
       , c.locationKey AS ChildLocationKey
FROM   location p LEFT JOIN location c
	  ON p.locationKey = c.locHubID
WHERE p.LocHubID = 0
ORDER BY p.locHubID
    ,p.LocationKey
    , c.locationKey
</cfquery>

Open in new window

Then use a grouped output to display the parent, then children:
<cfoutput query="getData" group="ParentLocationKey">
	Parent: #ParentLocationKey#<br>
	<cfoutput>
		<cfif ChildLocationKey gt 0>
		Child: #ChildLocationKey#<br>
		</cfif>
	</cfoutput>
</cfoutput>

Open in new window


This would be the result.  

Result:
Parent: 185
Child: 171
Child: 205
Child: 252
Parent: 225
Child: 1
Child: 2
Parent: 999

Open in new window



If that's not what you need, can you please explain further?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40529308
Hi Erik, I appreciate the points but since my answer was built on Simon's query, it should probably be a split :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now