472,789 Members | 856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,789 software developers and data experts.

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)
  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 1681

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

Similar topics

by: Ron Nolan | last post by:
I have a large application that contains lots and lots of financial history data. The history data is currently set up in a table called 'TblHist' that exists inside each of these three .mdb...
by: Don Leverton | last post by:
Hi All, I've got a situation where I am developing an Access 97 app for a client, and am in the "beta testing" stage. I have split the app up, using the DB splitter, into front-end /back-end...
by: Felix_WafyTech | last post by:
Hi, I'm working with the ObjectDatasource and the application is getting more and more chatty. Is there a way I could make the ObjectDatasource support multiple DataTables that could be...
by: HLCruz via AccessMonster.com | last post by:
I am working with a database that has client information separated in to 4 related tables - tFolder, tAddress, tEmail, tPhone number. In addition there are related tables tGifts and tCalls. The...
by: rewalk | last post by:
Hello all, I've set up protected Excel sheets that users can paste data into. I then need to be able to pull the data they paste into multiple access tables and then reset the Excel spreadsheet...
by: narendra vuradi | last post by:
Hi I have a requirement where in i haev to convert the SQL from Oracle to the one which will run on the SQL server. in the Oracle Query i am doing multiple joins, between some 13 tables. and...
by: beargrease | last post by:
I'm kind of comfortable with basic joins, but a current project requires a complex query of many tables. The GROUP_CONCAT(DISTINCT ...) function has been very useful as returning my values as comma...
by: modernshoggoth | last post by:
G'day all, I'm trying to update a single table (containing plans for flights) with information from the same and other tables (containing info for organisations and locations). tblFlightPlans...
by: John Roberts | last post by:
Hi, Is it possible to access a tables data over multiple joins? For instance, for each row of Table A, I want the associated data from Table D. Is this possible in access? If it's not, could you...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.