left join Aaron
----- Original Message ----- From: "Jamie Strachan" <[EMAIL PROTECTED]> Newsgroups: cfaussie To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Monday, April 26, 2004 4:06 PM Subject: [cfaussie] Output Problem > Hello Everyone, > > I have two tables and i need to have the data grouped by an ID based on > one of the tables. My problem in that i cannot get the data to show if > there is not a subID in the other table. > > Preferred Output > ********************* > > AgentName > --subAgentName1 > --subAgentName2 > > AgentName > --subAgentName1 > > AgentName > > AgentName > > etc.. > > Actual Output > ********************* > > AgentName > --subAgentName1 > --subAgentName2 > > AgentName > --subAgentName1 > > > I am not getting the agents who do not have a subagent listed shown. > > Here is my db structure/tables > > tblAGENTS > AgentID > AgentName > AgentPhone > AgentImage > > tblSUBAGENTS > SubID > SubAgentID > SubName > SubPhone > SubImage > > > Here is my query. > > <cfquery name="GetAgents" datasource="#application.dsn#" dbtype="ODBC"> > SELECT tblAGENTS.AgentID, > tblAGENTS.AgentName, > tblSUBAGENTS.SubAgentID, > tblSUBAGENTS.SubName > FROM tblAGENTS INNER JOIN tblSUBAGENTS ON tblAGENTS.AgentID = > tblSUBAGENTS.SubAgentID > ORDER BY tblAGENTS.AgentName > </cfquery> > > <cfoutput query="GetAgents" group="a_id"> > #AgentName# > <cfoutput>#SubName#</cfoutput> > </cfoutput> > > > > Any help or suggestions would be much appreciated. > > Jamie > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
