SQL连接返回重复的结果，而不是数据库中反映的结果

Question

I have three tables user, product, and userproduct.

User has the user profile information, product has a primary key, and userproduct has two heading for UserId and ProductId which I use to match up which user owns what product.

userproduct is set up in the following way:

----------------------------------
| UserId (int) | ProductId (int) |
----------------------------------
| 1           | 1                |
----------------------------------
| 1           | 2                |
----------------------------------

my PHP code is as follows:

$userId = 1;

$stmt = $pdo->prepare('SELECT * FROM product join userproduct on product.ProductId = userproduct.UserId where UserId = ?');
$stmt->execute([$userId]);
$products = $stmt->fetchAll();

foreach ($products as $productsOwned)
{
    echo $productsOwned['ProductName'];
}

I would assume this would echo out Product1, Product2. However, it echos out Product1, Product1.

I've looked over my database a few times and everything seems in order, but as I am still learning SQL I assume I have made a mistake there. Any guidance would be appreciated.

Answer 1

Your SQL is wrong, If you want to join this tables and get all the user products you can do this:

SELECT userproduct.UserId as userId, product.name as productName
FROM userproduct
JOIN product ON userproduct.ProductId = product.ProductId
WHERE userproduct.UserID = {$userId};