How to select rows from two tables using MySQL?-CodePudding

I want to select information from two SQL tables within one query.

table1

id | postID | status | number | userID | active
1  | 1      | 100    | 100    | 3      | 1
2  | 7      | 50     | 25     | 5      | 1
3  | 3      | 75     | 50     | 3      | 1

table2

postID | reference | joint_date | userID | remove
1      | 100       | 100        |  3     | 1
2      | 50        | 25         |  5     | 1
3      | 50        | 50         |  3     | 0

Expected Output

postID | status | number    | reference | joint_date
1      | 100    | 100       | 100       | 100
3      | 75     | 50        | 50        | 50

This is my try:

$userID = 12;
$active = 1;
$remove= 1; 
$sql = "SELECT table1.status, table1.number, table2.reference, table2.joint_date
FROM table1
WHERE table1.active=:active AND table1.userID=:userID
INNER JOIN table2 ON table1.postID=table2.postID
WHERE table2.postID=:userID 
AND table2.remove=:remove
ORDER BY table1.postID DESC";
$stmt = $this->pdo->prepare($sql);
    $stmt->bindValue(":active", $active, PDO::PARAM_INT);
    $stmt->bindValue(":userID", $userID, PDO::PARAM_INT);
    $stmt->bindValue(":remove", $remove, PDO::PARAM_INT);
    $stmt->execute();

What is wrong with my query?

CodePudding user response：

What is wrong with my query?

The clauses/statements order are wrong

SELECT....FROM....WHERE....AND....AND....ORDER BY..
You can not have two WHERE condition in the same SELECT (do not take in consideration subqueries)
There is no way you can get the expected result based on the given static variables
Based on the data example you have two condition which will never be true at the same time.

AND t2.postID=3 --- > t2.remove is equal to 0 then you specify AND t2.remove=1

You can use below example to properly filter based on your conditions:

SELECT t1.postID, 
       t1.status,
       t1.`number`, 
       t2.reference, 
       t2.joint_date
FROM table1 t1 
INNER JOIN table2 t2  ON t1.postID=t2.postID
WHERE t1.active=1 
AND t1.userID=3
ORDER BY t1.postID ASC;

Result:

postID    status  number  reference   joint_date
  1        100     100     100        100
  3        75      50       50        50

Note. I removed both conditions table2.postID=:userID AND table2.remove=:remove

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=78b0f07c7d1dd1db1008e415fc005e85