[SOLVED] Help with WHERE statement

[aussie]

Hi

I have 2 mysql statements.

My first statement displays only the product attributes that have a pd_id

 

It is the second statement that I am having the issue with.

 

A WHERE statement where I

display only items from the tbl_pd_attribute that dont have a

pd_id (productId) associated.

 

I only usually use really simple statements, easy to understand. Noob stuff.

 

mysql version 5.0.22_community_nt

 

First Statement functioning.

$sql= "SELECT *
        FROM tbl_pd_options_link opt, tbl_pd_attribute pda
         WHERE opt.pd_id = $productId AND opt.attribute_id = pda.attribute_id 
        ORDER BY pda.attribute_id";
        
        while($row= dbFetchAssoc($result)) {
    extract($row);

I have 3 tables

 

tbl_product has pd_id

 

tbl_pd_options_link

+----------------------+------+

| pd_id                   | 172  |           

+----------------------+------+

| attribute_id          | 2    |

+----------------------+------+

| attribute_name    | large|

+----------------------+------+

| attribute_checked  | 1    |      //or 0 if not checked

+----------------------+------+

 

tbl_pd_attribute

             

+----------------------+------+

| attribute_id          | 2    |

+----------------------+------+

| attribute_name    | large|

+----------------------+------+

| attribute_price      | 1    |

+----------------------+------+

 

  My second statement.  My Problem!

  So if in table tbl_pd_options_link opt there is no pd_id associated with an attribute_id

  show only those results from tbl_pd_attribute

 

  WHERE opt.pd_id = $productId this means it equals it?

  How to I make it say does not equal or not exist.

 

  There are 5 options/attributes that can be associated with a product.

  Some products will already have attributes (did that manually to set it up)

  One product may have 2 attributes which means there are 3 that can be chosen.

 

 

 

  $sql2= "SELECT *
        FROM tbl_pd_options_link opt, tbl_pd_attribute pda
  ///   WHERE opt.pd_id = $productId AND opt.attribute_id = pda.attribute_id 
        ORDER BY pda.attribute_id";
        
        while($row2= dbFetchAssoc($result2)) {
        extract($row2);
    

   

    I googled WHERE statements and looked through this forum, couldn't find an answer. Would appreciate any help. Thanks.

 

 

[celsoendo]

Why you repeat the column attribute_name on tbl_pd_options_link ? And why you have the column attribute_checked? It makes no sense. I think this table (tbl_pd_options_links) must have only 2 columns with primary key on these 2 columns: product_id and attribute_id. Then you store on this table ONLY the attributes that is checked for that product (n-n relationship).

 

Then you can use a sql statement like this to select the attributes of a product that were not checked (availabe attributes):

 

SELECT pda.* FROM tbl_pd_attribute pda WHERE NOT EXISTS (SELECT 1 FROM tbl_pd_options_link WHERE product_id = $productId AND attribute_id = pda.attribute_id);

 

The query above will return all attributes available for the product $productId.

Or you can return all attributes and an extra column saying if the attribute is checked or not:

 

SELECT opt.*, pda.*, IF(pda.attribute_id IS NULL, 'no', 'yes') attribute_checked FROM
tbl_pd_options_link opt
LEFT JOIN
tbl_pd_attribute pda ON (pda.attribute_id = opt.attribute_id)
WHERE
opt.product_id = $productId;

 

Got it?

[aussie]

Thankyou so much,

 

Why you repeat the column attribute_name on tbl_pd_options_link ? And why you have the column attribute_checked? It makes no sense. I think this table (tbl_pd_options_links) must have only 2 columns with primary key on these 2 columns: product_id and attribute_id. Then you store on this table ONLY the attributes that is checked for that product (n-n relationship).

I've repeated the column attribute_name just so it displays on my page

in this section, because I got confused with the id's

 

The column attributed_checked has the value of 1 = yes and 0 = no,

Because I think I may need that column when I go to call these attributes

in my shopping cart??

 

I understand why I would only need the 2 columns...

Then you can use a sql statement like this to select the attributes of a product that were not checked (availabe attributes):

SELECT pda.* FROM tbl_pd_attribute pda WHERE NOT EXISTS (SELECT 1 FROM tbl_pd_options_link WHERE product_id = $productId AND attribute_id = pda.attribute_id);

 

The query above will return all attributes available for the product $productId.

 

Or you can return all attributes and an extra column saying if the attribute is checked or not:

 

SELECT opt.*, pda.*, IF(pda.attribute_id IS NULL, 'no', 'yes') attribute_checked FROM
tbl_pd_options_link opt
LEFT JOIN
tbl_pd_attribute pda ON (pda.attribute_id = opt.attribute_id)
WHERE
opt.product_id = $productId;

 

Thankyou, Thankyou, This did it

 

SELECT pda.* FROM tbl_pd_attribute pda WHERE NOT EXISTS (SELECT 1 FROM tbl_pd_options_link WHERE product_id = $productId AND attribute_id = pda.attribute_id);

 

SELECT opt.*, pda.* (Didn't know you could write it like this)

That seems to be a much more efficient way. Will change other statements now!

 

(SELECT 1 FROM  ....Don't understand this or why it works, I would think that if I select 1

I would only get 1

 

The second statement with the LEFT JOIN and ON, I sort of understand LEFT JOIN but not ON.

will do some learning on that.