mck mck - 3 months ago 16
MySQL Question

Joining Tables in a ColdFusion Query

I am executing two queries to build a table off of data from two tables in the same database. The code I have now is below, but I know I am creating an unnecessary load doing it this way. I have been trying to join the tables to get the same result, but have had no luck. Any input?

<cfquery name="GetWeekends">
SELECT id, weekend_type, community_id, start_date, end_date, language
FROM _weekends
WHERE weekend_type = 1 and start_date > Now()
ORDER BY start_date ASC
</cfquery>

<cfloop query="GetWeekends">
<cfquery name="GetCommunity">
SELECT community_id, location, language, state, country
FROM _communities
WHERE community_id = #getweekends.community_id#
</cfquery>
<tr>
<td>#DateFormat(start_date, "mm/dd/yyyy")#</td>
<td>#GetComm.location#</td>
<td>#GetComm.state#</td>
<td>#GetComm.country#</td>
<td>#GetComm.language#</td>
</tr>
</cfloop>

Answer

Database joins are pretty basic. You'd do well to educate yourself about them.

In any event, it looks like you want to do something like this:

<cfquery name="GetWeekends">
SELECT w.id, w.weekend_type, w.community_id, w.start_date, w.end_date,
  w.language,
  c.community_id, c.location, c.language, c.state, c.country
FROM _weekends w
  INNER JOIN _communities c
    ON w.community_id=c.community_id
WHERE w.weekend_type = 1 and w.start_date > Now()
ORDER BY w.start_date ASC
</cfquery>