Need help with outputing query group names

[TheDerek17]

I am trying to come up with a way to output group headers, then all the records under each group header etc. It would be easy, except there is a twist with what i want to do.

Normally if I have this set of data (which I ‘borrowed’ from a site that showed the closest to what I’m looking for):

Example Table Setup:

TABLE [Numbers]

(Name, NUMBER)

Dave Bosky              843-444-4444

Dave Bosky              843-555-5555

Matthew Small        843-111-1111

Matthew Small      _843-222-2222

Matthew Small        843-333-3333

I could use the following code:

<cfoutput query="somequery" group="name">

        #name#<br>

        <cfoutput>

                #phonenumber#<br>

        </cfoutput>

        <hr>

</cfoutput>

And get this:

Dave Bosky

843-444-4444

843-555-5555

-------------------

Matthew Small

843-111-1111

843-222-2222

843-333-3333

-------------------

BUT, my actual tables are not set up like this. Rather than recording each name with each record, I would have an ID that is the foreign key for another table.

Current table set up would look like this:

TABLE [People]

(ID, NAME)

1 Dave Bosky        

2 Matthew Small  

TABLE [Phones]

(PEOPLE_ID, NUMBER)

1              843-444-4444

1              843-555-5555

2              843-111-1111

2              843-222-2222

2              843-333-3333

So this output would actually give me this with my current setup and the query code above:

1

843-444-4444

843-555-5555

-------------------

2

843-111-1111

843-222-2222

843-333-3333

-------------------

How do I keep my current setup but create a query that achieves the same result from the top? (Output the names from the People table as the group headers, but the data from the Phones table underneath that)

[TheRealAgentK · Accepted solution]

You need to join the two tables together and then group the output.

Something along the lines of (might vary slightly depending on your DB and exact table structure)

SELECT ppl.name, ph.number

FROM People ppl

INNER JOIN Phones ph ON ppl.id = ph.people_id

ORDER BY ppl.name

Cheers

Kai

[TheDerek17]

Thanks! With a little tweaking I got it done.

In case this might be useful to someone else this is the code I ended up using as a test run for what Im trying to do:

<cfquery name="testthis" datasource="IRLE">

SELECT ACCOUNTTYPES.ACCOUNTTYPEID, ACCOUNTTYPES.TYPEDESCRIPTION, ACCOUNTS.ACCOUNTNUMBER, ACCOUNTS.ACCOUNTTYPE, ACCOUNTS.ACCOUNTDESCRIPTION

FROM ACCOUNTS INNER JOIN ACCOUNTTYPES ON ACCOUNTTYPES.ACCOUNTTYPEID = ACCOUNTS.ACCOUNTTYPE

WHERE ACCOUNTUSERID = '#Session.UserID#'

</cfquery>

<table>

<th colspan="3" align="center"><u>ACCOUNTS:</u></th>

<cfoutput query="testthis" group="TYPEDESCRIPTION">

<tr><td></td>

<td colspan="2">#TYPEDESCRIPTION# Accounts:<BR></td>

</tr>

<cfoutput>

<tr>

<td></td>

<td>#ACCOUNTNUMBER#</td>

<td>#ACCOUNTDESCRIPTION#</td>

</td>

</tr>

</cfoutput>

</cfoutput>

</table>

Im using MySQL, so all the table names etc have to be capitalized.

Table setup:

ACCOUNTTYPES
ACCOUNTTYPEID	TYPEDESCRIPTION
1	CASH
2	TRADE

ACCOUNTS
ACCOUNTNUMBER	ACCOUNTUSERID	ACCOUNTTYPE	ACCOUNTDESCRIPTION
1	1	1	CASH
2	1	2	BROKERAGE

Note I also threw in a WHERE clause to only show accounts for the logged in user. (Session.USERID is defined by my code at login)

All in all the output was:

ACCOUNTS:

                CASH Accounts:

                1              CASH

                TRADE Accounts:

                2              BROKERAGE

Given my data at the moment, that’s what I wanted! Sweet!