Join Tables in CFQuery

[ryanbrand]

Hi,
I have 2 tables that I have to get data from. The first table will determine what data is pulled from the second. The problem is that I need to pull 3 records for each record pulled from the first.
Here's an example of the tables:

TABLE: areas

areaID personcode1 personcode2 personcode3
----------------------------------------------
A1 gjsmith hltier kdjones
A2 klbinn dlcope bknelson
A3 jkelms wacopper kdyoke


TABLE: people

code name
----------------
gjsmith Greg Smith
klbinn Kris Binn
jkemlms Josh Elms
hltier Hans Tier
dlcope David Cope
wacopper Bill Copper
kdjones Kristin Jones
bknelson Briah Nelson
kdyoke Kari Yoke

For each area I need to display the 3 names together. So far I can only display the areaID and one name. I would like to display like this:
A1-Greg Smith, Hans Tier, Kristin Jones
A2-Kris Binn, David Cope, Briah Nelson
etc.

Please help me. Thank you!!

Ryan

 

[r937]

this is a database query, and not a coldfusion query-of-queries, right?

[tt]select areaID
, p1.name as person1name
, p2.name as person2name
, p3.name as person3name
from areas
left outer
join people p1
on personcode1 = p1.code
left outer
join people p2
on personcode2 = p2.code
left outer
join people p3
on personcode3 = p3.code [/tt]

rudy

http://r937.com/

SQL Consulting

 

[ryanbrand]

Rudy,
I don't know what you meant by ColdFusion query-of-queries, but I'm using ColdFusion to get this information and display it on my page.
When I tried the code it gave me a syntax error. When I only had it try to left join with one of the people, the error was "Join Expression Not Supported".

Do you have any more ideas of what I can do?

Thank you for your help!!

Ryan

 

[r937]

the reason i asked about query-of-queries, this is the coldfusion forum

it appears that you have an access database problem (at least that's what i surmise from the error message)

please confirm it is access, and please show your latest version of the query



rudy

http://r937.com/

SQL Consulting

 

[ryanbrand]

That's correct that it is an access database and I'm using ColdFusion. Here's the query:
<CFQUERY datasource=&quot;#DB#&quot; name=&quot;joint&quot;>
select ucode
, p1.name as person1name
, p2.name as person2name
, p3.name as person3name
from areas
left outer
join counselors p1
on ADMemail = p1.email
left outer
join counselors p2
on FAemail = p2.email
left outer
join counselors p3
on second_FAemail = p3.email
</cfquery>


This is the second that gave me &quot;Join Expression Not Supported&quot;:
<CFQUERY datasource=&quot;#DB#&quot; name=&quot;joint&quot;>
select ucode
, p1.name as person1name
from areas
left outer
join counselors p1
on ADMemail = p1.email
</cfquery>

I appreciate your help with this. Thank you.
Ryan

 

[r937]

i'm sorry, i cannot see anything wrong

if the first one with 3 copies of the counselors table runs, then the second with only one of them should too

i know for a fact that the LEFT OUTER join works in access 97 and any version after that

you're not running access 2 or something, are you?

rudy

http://r937.com/

SQL Consulting

 

[ryanbrand]

Thanks for your help... I just might have to change my database to have the codes and the names in the one table. I have Access 2000, so I don't understand why it won't work. It won't be too difficult to add the names in, but the design won't be as good. That's ok though.

Thank you.
Ryan

 

[r937]

the codes and the names are in one table -- the people table

your design is correct

if you want, send me an mdb with your two tables in it and i'll have a look -- email is on my site

rudy

http://r937.com/

SQL Consulting