Can I do I mysql query across 3 tables sharing multiple primary keys?
I have this query to g开发者_运维百科rab all requests by user_id and grabbing the profile names from the profile_id's that are associated with the records retrieved.
SELECT Requests.request_id,Requests.user_id,Requests.profile_id,Requests.job_title,Requests.date,Requests.time,Requests.info,Requests.approval, Profile.first_name, Profile.last_name FROM Requests, Profile WHERE user_id = '$user_id' AND Requests.profile_id = Profile.profile_id ORDER BY approval ASC
What if I wanted to add another table to retrieve the names of the user_id from the Accounts table?
I think using joins would be a nice idea because it is more lucid.
SELECT Requests.request_id,Requests.user_id,Requests.profile_id,Requests.job_title,Requests.date,Requests.time,Requests.info,Requests.approval, Profile.first_name, Profile.last_name, OtherTable.other_field FROM Requests
LEFT JOIN Profile ON Requests.profile_id = Profile.profile_id
LEFT JOIN OtherTable ON Requests.profile_id = OtherTable.profile_id
WHERE Request.user_id = '$user_id' ORDER BY approval AS
I'm guessing user_id is a primary key within some users table and you want tables that also have a user_id reference indicating that this record belongs to that user.
Use MySQL JOIN. For example, you'd want to JOIN (a table) ON users.user_id = groups.user_id
Something like this?
select
r.request_id
, r.user_id
, r.profile_id
, r.job_title
, r.date
, r.time
, r.info
, r.approval
, p.first_name
, p.last_name
, a.something
from Requests as r
join Profile as p on p.profile_id = r.profile_id
join Accounts as a on a.user_id = r.user_id
where user_id = '$user_id'
order by r.approval asc ;
精彩评论