How do I get multiple hyperlinks to diplay in a query output?

[Guest]

I am using two tables to list all of the sport teams for a school. The sports table lists all of the team data grouped by gender. The second table uses the sportID from the first table to associate the team with a schedule.  The problem is not all teams have a schedule yet.

I am trying to display a link for those teams with schedules. I compare the two tables, sports & schedule and use a CFIF statement to compare the 2 sportIDs in the output to determine which teams have a schedule.  If the two sportIDs match then a hyperlink is displayed.

Unfortuanetely, only one link will be displayed, even though there should be three links. Below is my code:

Table to display the teams grouped by gender

<cfquery name="getSports" datasource="#application.database#">

select gender, team, levels, sportID

from sports

group by gender, team, levels, sportID

</cfquery>

Table used to get the sportID for schedules already created

<cfquery name="getID" datasource="#application.database#">

select sportID

from schedules

group by sportID

</cfquery>

I then compare the sportIDs and if they match, I then display a hyperlink

<cfoutput query="getSports" group="gender">

<h1">#gender#'s Teams</h1>

<ul>

<cfoutput>

     <li>

          <cfif getID.sportID EQ getSports.sportID><a href="teams.cfm?sportID=#sportID#"></cfif>

           #team# <cfif levels GT "">(#levels#)</cfif>   //levels is used to differentiate between J.V. and Varsity. Not all teams use levels

          <cfif getID.sportID EQ getSports.sportID></a></cfif>

     </li>

</cfoutput>

</ul>

</cfoutput>

How do I get the other teams to display their links?

[Miguel-F]

Only one link is being displayed because you are only comparing against the first record returned from your "getID" query. In your if statement <cfif getID.sportID EQ getSports.sportID> "getID.sportID" is the same as using "getID[1].sportID".  It only returns the first record.

You are looping over the "getSports" query but you need to compare those sportID's to each record returned from your "getID" query.  There are several ways you can do that.  Is this enough to get you going or do you need examples?

[Guest]

@Miguel - Yes, some examples would be helpful is you have any.

@wolfShade, I oriiginally had an inner join which worked as far as grabbing th esportID. I still need a way to comapre the two queries

[Miguel-F]

I'm hesitant to give you an example because there are so many ways to do this and I don't want to lead you in a direction that might not be appropriate for what you are trying to do (outside of this code block).

I think WolfShade is on the right track.  I would also use a single query with a join instead of trying to use a condition in a loop.  I think you would want an outer join instead of an inner join though.  You want all rows returned whether or not they have a schedule.  Right?  If so, you should be able to use an outer join and then your condition for the hyperlink becomes whether the schedules.sportID is NULL or not.  I think this would be the best way to handle this.

OR

If you can't do that for some reason, then you need to modify your cfif logic.  You could move your "getID" query inside of your <cfoutput query="getSports"... loop.  Add a where condition to the query "where sportID = getSports.sportID".  Then you can check if the getID.recordcount is greater than 0 (or equal to 1).  If so it has a schedule so create the hyperlink.  Not pretty though, because as you see it will need to query for each record in "getSports" query.

OR

You could keep your queries the same but place a cfloop around your cfif logic to check for each record in your "getID" query.  Again, not pretty because you have to loop over the "getID" query for each record in the "getSports" query.  Something like:

     <li>

          <cfloop query="getID">

          <cfif getID.sportID EQ getSports.sportID><a href="teams.cfm?sportID=#getSports.sportID#"></cfif>

           #team# <cfif levels GT "">(#levels#)</cfif>   //levels is used to differentiate between J.V. and Varsity. Not all teams use levels

          <cfif getID.sportID EQ getSports.sportID></a></cfif>

          </cfloop>

     </li>

OR

You could do a query of a query...  Lots of ways to skin a cat...

[WolfShade]

Would it be permissible to do a JOIN query between the two tables?  Seems to me that would be better.  Just MHO.

^_^

[Steve Sommers]

As previously mentioned, I would have the query do the work:

<cfquery name="getSports" datasource="#application.database#">

select gender, team, levels, sportID

from sports

where

  exists( select sportID from schedules where schedules.sportID=sports.sportID and schedules.team=sports.team  )

group by gender, team, levels, sportID

</cfquery>

I'm not sure if this query accurate as I don't see much detail on the format or relationship of schedules to sports.

[Guest]

I believe I neglected to tell you guys (but I think you figured it out) that I want to list all of the teams but only have links where they have a schedule to view.

[Miguel-F]

Yep, that's why I was saying to use an outer join.  If there are no records in the schedules table then the query will return NULL for that row.  I was thinking something like this (note that you need to return some column from the schedules table to check against later):

<cfquery name="getSports" datasource="#application.database#">

select sports.gender, sports.team, sports.levels, sports.sportID, schedules.someColumn

from sports

left outer join schedules on sports.sportID = schedules.sportID

group by gender, team, levels, sportID

</cfquery>

Then your condition will become something like:

<cfif getSports.someColumn NEQ "">

[Guest · Accepted solution]

Thanks gentlemen, SInce everyone was suggesting an outer join, I pulled out my trusty ColdFusion manual and read up on them. I added the sum() to the query:

<cfquery name="getSports" datasource="#application.database#">

select sports.gender, sports.team, sports.levels, sports.sportID, sum(schedules.sportID) AS scheduled

from sports

left outer join schedules on sports.sportID = schedules.sportID

group by sports.gender, sports.team, sports.levels, sports.sportID

</cfquery>

Then added the following to the output:

<cfif getSports.scheduled NEQ ""><a href="teams.cfm?sportID=#sportID#"></cfif>

And it all works perfectly. Life is good again!!!