Skip to main content
N
Newsgroup_User
Inspiring
September 3, 2008
Question

SQL or CFOUTPUT Help Needed

  • September 3, 2008
  • 4 replies
  • 366 views
I have two tables in a MySQL database, members and committees.

members table
----------------
member_id
member_first_name
member_last_name

committees table
-----------------
committee_id
committee_name
committee_leader_member_id
committee_co_leader_member_id

I need to display the committees and the leader and co-leader names on a
page.

Right now my SQL looks like this:

SELECT c.committee_id, c.committee_name, c.committee_leader_member_id,
c.committee_co_leader_member_id, m.member_first_name, m.member_last_name
FROM committees c
LEFT JOIN members m
ON c.committee_leader_member_id = m.member_id
OR c.committee_co_leader_member_id = m.member_id

And this is my CFOUTPUT

<cfoutput query="rsCommittees" group="committee_name">
<tr>
<td><strong>Committee Name</strong>: #rsCommittees.committee_name#<br>
<strong>Committee Co-Leader</strong>: <cfif
rsCommittees.committee_leader_member_id NEQ
"">#rsCommittees.committee_leader_member_id#
#rsCommittees.member_last_name#,
#rsCommittees.member_first_name#<cfelse>VACANT</cfif><br>
<strong>Committee Co-Leader</strong>: <cfif
rsCommittees.committee_co_leader_member_id NEQ
"">#rsCommittees.committee_co_leader_member_id#
#rsCommittees.member_last_name#,
#rsCommittees.member_first_name#<cfelse>VACANT</cfif>
</td>
</tr>
</cfoutput>

The #rsCommittees.committee_leader_member_id# and
#rsCommittees.committee_co_leader_member_id# numbers appear correctly, but I
can't figure out how to get the names to display correctly.

Anyone help?

--
Ken Ford
Adobe Community Expert Dreamweaver/ColdFusion
Adobe Certified Expert - Dreamweaver CS3
Fordwebs, LLC
http://www.fordwebs.com


    This topic has been closed for replies.

    4 replies

    D
    Dan_Bracuk
    Inspiring
    September 3, 2008
    Another approach is to join to the members table twice, once for the leader and once for the co-leader.
    N
    Newsgroup_UserAuthor
    Inspiring
    September 3, 2008
    welcome :)
    my forehead gets pretty sore at times, too...

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    N
    Newsgroup_UserAuthor
    Inspiring
    September 3, 2008
    OMG don't i feel stupid!!!!!!!

    <cfoutput query="rsCommittees" group="committee_name">
    <tr>
    <td colspan="6">#rsCommittees.committee_name#</td>
    </tr>
    <cfoutput>
    <tr>
    <td>#rsCommittees.committee_id#</td>
    <td>#rsCommittees.committee_leader_member_id#</td>
    <td>#rsCommittees.committee_co_leader_member_id#</td>
    <td>#rsCommittees.member_first_name#</td>
    <td>#rsCommittees.member_last_name#</td>
    <td>#rsCommittees.member_id#</td>
    </tr>
    </cfoutput>
    </cfoutput>

    Thanks for smacking me in the forehead Azadi

    --
    Ken Ford
    Adobe Community Expert Dreamweaver/ColdFusion
    Adobe Certified Expert - Dreamweaver CS3
    Fordwebs, LLC
    http://www.fordwebs.com


    "Azadi" <azadi@sabai-dee.com> wrote in message
    news:g9l4aa$3qd$1@forums.macromedia.com...
    > don't you need a nested <cfoutput> to show the names for a grouped
    > committee_name?
    >
    > if that does not solve it, try a union query instead? get leader in the
    > first select, co-leader in the 'unioned' select, maybe adding some extra
    > column to define who is leader and who is co-leader (if you need to):
    >
    > (SELECT c.committee_id, c.committee_name, c.committee_leader_member_id
    > AS mid, m.member_first_name, m.member_last_name, 1 AS is_leader
    > FROM committees c LEFT JOIN members m ON c.committee_leader_member_id =
    > m.member_id)
    > UNION
    > (SELECT c.committee_id, c.committee_name,
    > c.committee_co_leader_member_id AS mid, m.member_first_name,
    > m.member_last_name, 0 AS is_leader
    > FROM committees c LEFT JOIN members m ON c.committee_co_leader_member_id
    > = m.member_id)
    >
    > hth
    >
    > Azadi Saryev
    > Sabai-dee.com
    > http://www.sabai-dee.com/

    N
    Newsgroup_UserAuthor
    Inspiring
    September 3, 2008
    don't you need a nested <cfoutput> to show the names for a grouped
    committee_name?

    if that does not solve it, try a union query instead? get leader in the
    first select, co-leader in the 'unioned' select, maybe adding some extra
    column to define who is leader and who is co-leader (if you need to):

    (SELECT c.committee_id, c.committee_name, c.committee_leader_member_id
    AS mid, m.member_first_name, m.member_last_name, 1 AS is_leader
    FROM committees c LEFT JOIN members m ON c.committee_leader_member_id =
    m.member_id)
    UNION
    (SELECT c.committee_id, c.committee_name,
    c.committee_co_leader_member_id AS mid, m.member_first_name,
    m.member_last_name, 0 AS is_leader
    FROM committees c LEFT JOIN members m ON c.committee_co_leader_member_id
    = m.member_id)

    hth

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices