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
- Table Profile
- profiletype (SQL ID)
- phoneprovider (SQL ID)
- Table DropDowns
- ID
- name
- ddvalue
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
- <cfquery name="newusers" datasource="#application.dsn#">
- SELECT Profile.ID, Profile.username, DropDowns.ddvalue2
- FROM Profile
- JOIN DropDowns
- ON Profile.profiletype = DropDowns.ID
- </cfquery>
Expand|Select|Wrap|Line Numbers
- <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>
Expand|Select|Wrap|Line Numbers
- <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>