Loop? 1

[boatguy]

I have a table called events and am displaying past, present and upcoming events to site visitors. I also have a table called entries where coaches can post play by play commentary about a particular event. Up until today, I was only listing the event name for future events as opposed to linking to the commentary page as I never expected there would be commentary posted for a future event. Now, they want me to check to see if a record exists in the entries tables and if so, link the event name to the commentaries page.

Current code looks like:

<CFQUERY name="future" datasource="#dsn#">
SELECT *
FROM Events
WHERE datediff(d, eventdate, getdate()+ 1) < 1
AND OrgID= '#ORGID#'
ORDER BY eventdate,eventname
</cfquery>

<table>
<cfoutput query=”future”>
<tr>
<td align="left" valign="top">
<font size="2" face="Georgia, Times New Roman, Times, serif">
<a href="[URL unfurl="true"]http://www.webscore.net/rb/commentary.cfm?eventid=#eventid#&orgid=#orgid#")">#EventName#</a>[/URL]
</font></tr>
</cfoutput>
</table>

So, here’s the synopsis:
I query Events table
I then need to query the entries table to see if there are any entries matching the event ID
If there are…include the a tag

I guess I need to add a second query (or query within a query) of the entries table I am just not sure how do it.

I hope I am clear enough.

 

[r937]

no, what you want is a join

SELECT [i]foo, bar, qux[/i]
  FROM Events
left outer
  join Comments
    on Events.eventid = Comments.eventid   
 WHERE ...

r937.com | rudy.ca

 

[boatguy]

Thanks - I have the Outer Join in the query, but I am still unsure how I put an if statement in the output to add the <a> tag when records are matched in the outer join.

 

[r937]

if you wanted to show the "commentary" column from the query (let's assume it's a text column), you would use this --

<cfoutput query="future">
<tr><td><a href="

http://www.webscore.net/rb/commentary.cfm?eventid=#eventid#&orgid=#orgid#">#EventName#</a>

<cfif Len(commentary)>#commentary#</cfif></td></tr>
</cfoutput>

if instead you wanted to show another link, then replace the "commentary" column with the name of the column that contains the url, and adjust the CFIF accordingly

r937.com | rudy.ca

 

[boatguy]

The problem is, I don't want to show a column.
I have 2 tables, Events and Entries.
Here's the current Query:

<CFQUERY name="future" datasource="#dsn#">
SELECT * FROM Events
LEFT OUTER JOIN ENTRIES
On Events.eventid = Entries.eventid 
WHERE datediff(d, Events.eventdate, getdate()+ 1) < 1
AND Events.OrgID= '#ORGID#'
ORDER BY eventdate,eventname
</cfquery>

What I need to do is to put a link on Events.eventname if the eventID is found in the entries table. So, it would be something like a record count. The eventID will always exisit in the Events table, but there may or may not be records matching the event id in the entries table.

 

[r937]

if there may or may not be entries, and you don't want to show a column, what would you construct the link out of? where would the link go to?

tip: don't use "select star", always list the specific columns that you want the query to return, that way, when you have to list them, you will already have listed them

in this case you are returning all the columns of the entries table, but apparently don't want to show any of them

if there are multiple entries per event, and you want show just a record count, then you must have a GROUP BY, and this further requires that you include in the GROUP BY all non-aggregate columns, so the tip above isn't really a tip, it's a requirement

r937.com | rudy.ca

 

[boatguy]

The link is basically static with the exception of passing the Eventid and OrgID. Even if the record count in the Entries table = 0, I still need to show the event name from the Events Table. Like this:

IF A MATCHING EVENT ID IS FOUND IN THE ENTRIES TABLE <a href="thelink?eventid=#eventid#&Orgid=#ordid#">Event Name</a> ELSE Even Name

I will list the fields and remove the star.

 

[r937]

so, you will have to alias the Entries.eventid column in the query --

<cfquery ...>
select Events.eventid
, Events.eventname
, Events.oid
, Entries.eventid as entries_eventid
from ...
</cfquery>

and then you can do this --

<cfoutput query="future">
<tr><td><cfif Len(entries_eventid)><a href="

http://www.webscore.net/rb/commentary.cfm?eventid=#eventid#&orgid=#orgid#">#EventName#</a>

<cfelse>#EventName#</cfif></td></tr>
</cfoutput>

r937.com | rudy.ca

 

[boatguy]

I greatly thank you for your help!

 

[r937]

oops, just realized, the query above will return one row per Entries row! you want to group

<cfquery ...>
select Events.eventid
, Events.eventname
, Events.oid
, count(Entries.eventid) as entries_count
from ...
group by Events.eventid
, Events.eventname
, Events.oid
</cfquery>

and then you can do this --

<cfoutput query="future">
<tr><td><cfif entries_count GT 0><a href="

http://www.webscore.net/rb/commentary.cfm?eventid=#eventid#&orgid=#orgid#">#EventName#</a>

<cfelse>#EventName#</cfif></td></tr>
</cfoutput>

r937.com | rudy.ca