MySQL select user motto

I am stuck on this problem and I have been trying to solve for like 2 hours.

I have a posts table which contains all posts. Each post has a column called ownerid which keeps the id of the owner of this post. And it also contains a column called groupid.

What I want to have is to select all posts with a specific groupid (eg. 2) and the motto of the user which is in users table.

So something like this.

SELECT * FROM posts WHERE groupid = 2

SELECT motto FROM users WHERE userid = $row['ownerid']

JavaScript
​x
 
SELECT * FROM posts WHERE groupid = 2​SELECT motto FROM users WHERE userid = $row['ownerid']​

But both queries in one line, because I’m using while loop to fetch rows.

$stmt->bind_result($postid, $motto, $groupid, $text, $postdate);

JavaScript
 
$stmt->bind_result($postid, $motto, $groupid, $text, $postdate);​

Answer

If I undestand correctly your question, you should try

SELECT P.*, U.motto from posts P INNER JOIN users U on P.ownerid = U.userid 
WHERE P.groupid = 2;

JavaScript
 
SELECT P.*, U.motto from posts P INNER JOIN users U on P.ownerid = U.userid WHERE P.groupid = 2;​

Advertisement

Answer