Multiple joins between 2 tables and profiles with multiple users

[kalinda]

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.

Table Profile
profiletype (SQL ID)
phoneprovider (SQL ID)

Table DropDowns
ID
name
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:

<cfquery name="newusers" datasource="#application.dsn#">
   	 SELECT Profile.ID, Profile.username, DropDowns.ddvalue2 
     FROM Profile
     JOIN DropDowns
     	ON Profile.profiletype =  DropDowns.ID
</cfquery>

The problem I'm having is when I try to reference more than one DropDown relationship.

<cfquery name="oneuser" datasource="#application.dsn#">
	SELECT Profile.*, DDprofiletype.ddvalue2 AS d1, DDphoneprov.ddvalue AS d2
    FROM Profile
    JOIN DropDowns DDprofiletype ON Profile.profiletype = DDprofiletype.ID
    JOIN DropDowns DDphoneprov ON Profile.phoneprov = DDphoneprov.ID
    ORDER BY Profile.username
</cfquery>

And I'm trying to list the Profiles with their respective users here, but it isn't working right either.

<cfquery name="allusers" datasource="#application.dsn#">
     SELECT Profile.ID, Profile.username, Profile.profiletype, Profile.barcode,  Users.fname, Users.lname, DropDowns.ddvalue2, Profile.avatar
     FROM Profile
     JOIN DropDowns
        ON Profile.profiletype = DropDowns.ID
     RIGHT OUTER JOIN Users
        ON Profile.ID = Users.profile_ID
     ORDER BY Profile.username
</cfquery>