[RESOLVED] Using multiple where statements with different tables

[shure2]

Hi,

I want to select some product details and put them into a table.

I do this by having a lead table, option table, and product table.

There can be many options to a lead (so i store leadid in the option table), and many products to an option (i store the prodid in the option table)

PROBLEM:
I have the following statement, which somewhat works, but the same products are outputted into the table an infinite amount of times (see below for example table)

Name Code Category
Honda Jazz HJ1 Car
Ducati monster DC1 Motorbike
Honda Jazz HJ1 Car
Ducati monster DC1 Motorbike
Honda Jazz HJ1 Car
Ducati monster DC1 Motorbike
Honda Jazz HJ1 Car
Ducati monster DC1 Motorbike
Honda Jazz HJ1 Car
Ducati monster DC1 Motorbike

etc

P.S. is 'option' a reserved name in sql, because when im referencing the option table I need to use my database name with it

<?php
                    require "connect.php";
                    $query =  "SELECT product.prodname, product.prodcode, product.prodcategory
                               FROM product, lead, `mct`.`option`
                               WHERE option.leadid = 1 AND option.optionnumber = 1 AND product.prodid = option.partid AND option.type = 'product'";
                    $result = @mysql_query($query, $connection) or die ("Unable to perform query.<br />$query<br/>".mysql_error());

                ?>

                <table border="1">
                    <tr>
                        <th>Name</th>
                        <th>Code</th>
                        <th>Category</th>
                    </tr>
                <?php

                    while($row= mysql_fetch_array($result))
                    { ?>
                        <tr>
                            <td ><?php echo $row['prodname']?></td>
                            <td ><?php echo $row['prodcode']?></td>
                            <td ><?php echo $row['prodcategory']?></td>
                        </tr>
                     <?php
                     } ?>
                    </table>

[Lars_Berg]

Why do you have the lead table in the query?

P.S. is 'option' a reserved name in sql

No.

It is in Mysql though.

[shure2]

hi, thanks for your reply

I have managed to solve my problem using the below code:

$query =  "SELECT product.prodid, product.prodname, product.prodcode, product.prodcategory, produnitprice, option.partnumber
                    FROM
  product
INNER JOIN
  `option`
ON
  product.prodid = option.partid
INNER JOIN
  lead
ON
  lead.leadid = option.leadid

                WHERE
                    option.type = 'product'
                    AND option.optionnumber = '".$f."'
                    AND option.leadid = 1
                    ORDER BY option.partnumber";