inner join gives back multiple values

Question

I have products, All of these products have for example. An id, name and price. All of these products are connected in the database like this:

Since I don't have 10 reputation... Here is a link to a picture http://puu.sh/oS95c/b7b5b17427.png

What I want to achieve is to have products that are connected to another product show up on the screen using inner join. However instead of getting the connected items back I get back every item of the right column even if they are not connected.

Here is a link to get a better view of the database: http://puu.sh/oSBF2/1af1ce3751.png

$sql = "SELECT   AFBEELDING_KLEIN, PRODUCTNAAM, PRIJS
    FROM PRODUCT
    inner JOIN PRODUCT_GERELATEERD_PRODUCT
    ON PRODUCT.PRODUCTNUMMER=PRODUCT_GERELATEERD_PRODUCT.PRODUCTNUMMER_GERELATEERD_PRODUCT";

$result = sqlsrv_query($db, $sql);
$data = sqlsrv_fetch_array($result);

while($data = sqlsrv_fetch_array($result)) {
    $big_picture = '<img src="../' . $data["AFBEELDING_KLEIN"] . '"' . 'alt="product">';
    $link = '<a href="../productpaginas/' . $data["PRODUCTNAAM"] . '.php"<p>&nbsp;' . $data["PRODUCTNAAM"] . '</p></a>';
    $price = '<h2>&nbsp; &#8364;' . $data["PRIJS"] . '</h2>';

    echo '<div class="product">';
    echo $big_picture;
    echo $link;
    echo $price;
    echo '</div>';
}

Why not share the text output you get? (You can change the prices if you want, or remove the column, but nothing else.) — Julie Pelletier, May 15 '16 at 01:10
Please show the SQL statement used to generate the result in the graphic. — Pinke Helga, May 15 '16 at 01:12
Which tables do belong to products and which colums are defined for each of those? Best would be showing the table definitions. — Pinke Helga, May 15 '16 at 01:27

score 0 · Accepted Answer · answered May 15 '16 at 01:38

0

Since you immune to questions and don't provide more information, we can only guess.

You might want a m:n link back to the product table itself.

SELECT
  p1.PRODUCTNUMMER, p2.AFBEELDING_KLEIN, p2.PRODUCTNAAM, p2.PRIJS
FROM
  PRODUCT AS p1
INNER JOIN
  PRODUCT_GERELATEERD_PRODUCT AS pgp
ON
  p1.PRODUCTNUMMER = pgp.PRODUCTNUMMER
INNER JOIN
  PRODUCT AS p2
ON
  pgp.PRODUCTNUMMER_GERELATEERD_PRODUCT = p2.PRODUCTNUMMER
;

answered May 15 '16 at 01:38

Pinke Helga

6,378
2
22
42

i am really sorry that i couldn't reply. here is another link to maybe get a better view on the database http://puu.sh/oSBF2/1af1ce3751.png. want for example – MiauwSaysTheCat May 15 '16 at 11:34
Please edit your question and add the link there, so other people can find it easily as well. It looks as you wanted some sort of query as I've posted. – Pinke Helga May 15 '16 at 11:41
is that i want to get product(s) back based on the relations defined in the table "PRODUCT_GERELATEERD_PRODUCT". For example if you look at the first screenshot : if i am at the page of product 1 i would want to get back the information of product 2 and 3. The query you posted brings me a little closer but now i want to show only the first 2. Thank you for the help – MiauwSaysTheCat May 15 '16 at 11:42
MS SQL Server should have something similar as the `LIMIT` clause in MySQL. In worst case you would only fetch 2 rows and break the loop. See http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server to find a proper solution depending on your server version. – Pinke Helga May 15 '16 at 11:50

inner join gives back multiple values

1 Answers1