468,134 Members | 1,216 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,134 developers. It's quick & easy.

Multiple joins between 2 tables and profiles with multiple users

I am trying to make multiple joins between two tables and am at a loss. I've tried a variety of things with this being the latest, but it pulls all records multiple times.

Basically I have a main table of profiles that have several descriptors that are referenced with a SQL ID that is in a DropDown table.

Expand|Select|Wrap|Line Numbers
  1. Table Profile
  2. profiletype (SQL ID)
  3. phoneprovider (SQL ID)
  4.  
  5. Table DropDowns
  6. ID
  7. name
  8. ddvalue
The SQL ID for the different things in the Profile match the IDs in the DropDowns table (For example profiletype ID in the Profile table would be 37 and in the DropDowns table 37 as an ID is a profiletype and the ddvalue is "member". I want to be able to join the tables and have it display the ddvalue not the ID and I have multiple fields like this in the Profile table)

To further add to this mix...I have a table of Users that have the Profile ID attached to them. So you can have multiple users attached to each profile. And then I have drop downs inside the Users table I want to reference like described above.

Here is the query I have gotten to work with 1 DropDown join:

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="newusers" datasource="#application.dsn#">
  2.         SELECT Profile.ID, Profile.username, DropDowns.ddvalue2 
  3.      FROM Profile
  4.      JOIN DropDowns
  5.          ON Profile.profiletype =  DropDowns.ID
  6. </cfquery>
The problem I'm having is when I try to reference more than one DropDown relationship.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="oneuser" datasource="#application.dsn#">
  2.     SELECT Profile.*, DDprofiletype.ddvalue2 AS d1, DDphoneprov.ddvalue AS d2
  3.     FROM Profile
  4.     JOIN DropDowns DDprofiletype ON Profile.profiletype = DDprofiletype.ID
  5.     JOIN DropDowns DDphoneprov ON Profile.phoneprov = DDphoneprov.ID
  6.     ORDER BY Profile.username
  7. </cfquery>
And I'm trying to list the Profiles with their respective users here, but it isn't working right either.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="allusers" datasource="#application.dsn#">
  2.      SELECT Profile.ID, Profile.username, Profile.profiletype, Profile.barcode,  Users.fname, Users.lname, DropDowns.ddvalue2, Profile.avatar
  3.      FROM Profile
  4.      JOIN DropDowns
  5.         ON Profile.profiletype = DropDowns.ID
  6.      RIGHT OUTER JOIN Users
  7.         ON Profile.ID = Users.profile_ID
  8.      ORDER BY Profile.username
  9. </cfquery>
May 11 '10 #1
0 1481

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Felix_WafyTech | last post: by
4 posts views Thread by HLCruz via AccessMonster.com | last post: by
2 posts views Thread by narendra vuradi | last post: by
27 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.