there are two tables
user table fields:
id,username,name
article table field:
id,art_title,art_content,posterId,controllerId
posterId in the article table is a foreign key, pointing to id
in the user table, controllerId in the article table is also a foreign key, and also pointing to id in the user table
for example, there is a piece of data in article:
art_id | art_title | art_content | posterId | controllerId
1 | title | content | 1 | 2
there are two pieces of data in the user table
user_id | username | name
1 | leo |
2 | john |
I want to look up this data in article now, but I hope to find out the information of poster and controller through posterId and controllerId. How do I write the sql sentence?
my search statement now looks like this:
SELECT article.*,user.*
FROM article
LEFT JOIN user
ON article.id = user.id
WHERE art_id = 1the result is:
art_id=>1,
art_title=>title,
art_content=>content,
posterId=>1,
controllerId=>2,//sqlcontrollerIdcontroller,sql
user_id=>1, //posterIdposter
username=>leo, //posterIdposter
name=> //posterIdposter