开发者

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 ;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜