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>

[Mchl]

You did not specify any limiting condition between `lead` table and other tables, as a result you get a Cartesian product of all records from `lead` with records from other tables.

 

For list of MySQL reserved words see here:

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

(yes, OPTION is among them)

[shure2]

You did not specify any limiting condition between `lead` table and other tables, as a result you get a Cartesian product of all records from `lead` with records from other tables.

 

For list of MySQL reserved words see here:

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

(yes, OPTION is among them)

 

thanks! what sort of join type would you advise me to use?

[Mchl]

What sort of join you need?

[shure2]

What sort of join you need?

 

well I need to display the product name, product code and product category for one product id, achieving what I have above but without the entries being repeated over an over.

 

I have started to have a try at just a join, but get an error:

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

 

Error: Not unique table/alias: 'lead'

 

thanks so much for your help so far

[Mchl]

SELECT 
  product.prodid, product.prodname, product.prodcode, product.prodcategory
FROM 
  product
INNER JOIN
  lead
ON 
  product.prodid = lead.prodid
INNER JOIN 
  `option` 
ON 
  lead.leadid = option.leadid
WHERE 
  option.type = 'product' 
  AND option.optionnumber = 1 
  AND option.leadid = 1  

[shure2]

SELECT 
  product.prodid, product.prodname, product.prodcode, product.prodcategory
FROM 
  product
INNER JOIN
  lead
ON 
  product.prodid = lead.prodid
INNER JOIN 
  `option` 
ON 
  lead.leadid = option.leadid
WHERE 
  option.type = 'product' 
  AND option.optionnumber = 1 
  AND option.leadid = 1  

 

thanks for showing me a good technique and the right join, however there is no column lead.prodid so i get an error.

 

the joining fields are

- option.leadid

- option.prodid

 

do I need to somehow join both tables to the 'option' table? (i got a Not unique table/alias: 'option' when i tried it, obviously im doing it wrong!)

[Mchl]

SELECT
  product.prodid, product.prodname, product.prodcode, product.prodcategory
FROM
  product
INNER JOIN
  `option`
ON
  product.prodid = option.prodid
INNER JOIN
  lead
ON
  lead.leadid = option.leadid
WHERE
  option.type = 'product'
  AND option.optionnumber = 1
  AND option.leadid = 1  

[shure2]

SELECT
  product.prodid, product.prodname, product.prodcode, product.prodcategory
FROM
  product
INNER JOIN
  `option`
ON
  product.prodid = option.prodid
INNER JOIN
  lead
ON
  lead.leadid = option.leadid
WHERE
  option.type = 'product'
  AND option.optionnumber = 1
  AND option.leadid = 1  

 

thanks, worked perfectly!