Fetching information from a database

Question

This is going to be a bit of a noob question but I have a problem that when I want to fetch data according to a foreign key in another table, and the problem is I cannot figure out the sql to do it.

I have the following tables:

• USERS: login, password, role_id, userDetail_id

• ROLES: role_id, role_name

• USER_DETAILS: userDetail_id, name surname

Now I want to get the name and surname of all the users where the role_id is 4 and display them in an asp drop-down control.

Thanks for any help because it really baffled me.

Answer 1

SELECT ud.name, ud.surname 
FROM user_details ud, roles uroles, users u
WHERE ud.userDetail_id = u.userDetail_id 
AND u.role_id = uroles.role_id 
AND uroles.role_id = 4

Answer 2

Your sql will be :

 DataTable dt =   @"Select name, surname from USER_DETAILS as ud
    Inner Join USERS as u on ud.userDetail_id = u.userDetail_id
    Inner join ROLES as r on u.role_id = r.role_id
    where u.role_id = 4";

Then you can bind your datatable to dropdown :

    dropdown.DataSource = dt;
    dropdown.DataTextField = "name";
    dropdown.DataBind();

Answer 3

SELECT name, surname
FROM USER_DETAILS ud, USERS u
WHERE ud.userDetail_id = u.userDetail_id
 AND u.role_id = 4;