cpwilson
asked on
Problems with data join
Having problems with output from table join. Have 2 tables with a shared field (typeid) but when outputing only shows 1 record from the primary table. Code being used is as follows
<cfquery name="get_problems" datasource='#conn#'>
SELECT w.typeid, w.problem_type, c.typeid, c.problem_area
FROM problem_types w
INNER JOIN problem_areas c
ON w.typeid=c.typeid
ORDER BY w.problem_type ASC
</cfquery>
<cfoutput query='get_problems'>
#problem_type#<br>
<ul>
<cfoutput>
<li>#problem_area#</li>
</cfoutput>
</ul>
</cfoutput>
<cfquery name="get_problems" datasource='#conn#'>
SELECT w.typeid, w.problem_type, c.typeid, c.problem_area
FROM problem_types w
INNER JOIN problem_areas c
ON w.typeid=c.typeid
ORDER BY w.problem_type ASC
</cfquery>
<cfoutput query='get_problems'>
#problem_type#<br>
<ul>
<cfoutput>
<li>#problem_area#</li>
</cfoutput>
</ul>
</cfoutput>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that did not quite fix my problem. can see the issue here http://rifairhousing.org/test2/test.cfm
ASKER
doing the left join worked perfectly. thanks
Ohhh.. you meant the query is only returning a record IF the id exists in the primary table? Sorry, that wasn't clear. Yep, for that issue, outer join is what you need.
The outer cfouput is missing the "group" attribute:
<cfoutput query='get_problems'
Group="problem_type">
#problem_type#<br>
.....
</cfoutput>