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

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

Open in new window

erikTsomik

ASKER

Is there any advices

Simon

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ão

Can you explain where do you need help?
In SQL or HTML?

erikTsomik

ASKER

the help needed in sql. By simply joining to itself I only get records where locHubID = 0 I alaso need all records

Vitor Montalvão

Please send a desired output based on the example you posted.

Simon

+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).

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

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