title: Joining two tables in a SELECT query is not working
body: <p>I run a shopping cart where products can have additional options. For example, <em>product1</em> can have multiple options like:</p>
  
  <ul>
  <li><p>Sizes:
  s,m,l,xl</p></li>
  <li><p>Color:
  red,blue,green</p></li>
  </ul>
  
  <p>The default product is stored in <code>customers_basket</code>.</p>
  
  <p>The additional chosen items are stored in <code>customers_basket_attributes</code>.</p>
  
  <p>In both tables I insert a unique key called <code>products_unique_id</code>.</p>
  
  <p>This is to identify whenever a customer adds another product with the same option, if so
  I simply need to update the quantity field of the <code>customers_basket</code> table.</p>
  
  <p>If the user inserts the same product but with different options, the <code>products_unique_id</code> changes. </p>
  
  <p>The following query doesn't work:</p>
  
  <pre><code>SELECT * FROM 
  customers_basket_attributes cba, 
  customers_basket cb
  
  WHERE 
  
  cba.products_id = cb.products_id 
  AND 
  cba.products_unique_id = cb.products_unique_id 
  AND customers_id="1"
  </code></pre>
  
  <p>However, this query works:</p>
  
  <pre><code>SELECT * FROM 
  customers_basket cb, 
  products p, 
  products_description pd 
  WHERE 
  cb.products_id = p.products_id 
  AND 
  cb.products_id = pd.products_id 
  AND customers_id="1"
  </code></pre>
  
  <p>Also this works:</p>
  
  <pre><code>SELECT * FROM 
  customers_basket_attributes cba, 
  products p, 
  products_description pd 
  WHERE 
  cba.products_id = p.products_id 
  AND 
  cba.products_id = pd.products_id 
  AND customers_id="1"
  </code></pre>
  
  <p><strong>Database Structure &amp; Records</strong></p>
  
  <p>The following is the database structure and records needed to test the queries above:</p>
  
  <pre><code>DROP TABLE IF EXISTS customers_basket;
  CREATE TABLE customers_basket (
    customers_basket_id INTEGER PRIMARY KEY ASC,
    customers_id int NOT NULL,
    products_id int NOT NULL,
    products_unique_id tinytext NOT NULL,  
    customers_basket_quantity int(2) NOT NULL,
    final_price decimal(15,4),
    customers_basket_date_added datetime
  );
  
  DROP TABLE IF EXISTS customers_basket_attributes;
  CREATE TABLE customers_basket_attributes (
    customers_basket_attributes_id INTEGER PRIMARY KEY ASC,
    customers_id int NOT NULL,
    products_id int NOT NULL,
    products_unique_id tinytext NOT NULL,
    products_options_id int NOT NULL,
    products_options_txt varchar(64) NOT NULL default '',
    products_options_value_id int NOT NULL,
    products_options_value_txt varchar(64) NOT NULL default ''
  );
  </code></pre>
  
  <p>In addition, I've included the tables below even though they shouldn't be needed to test the problem query.</p>
  
  <pre><code>DROP TABLE IF EXISTS products;
  CREATE TABLE products (
    products_id int NOT NULL,
    products_quantity int(4) NOT NULL,
    products_model varchar(12),
    products_image varchar(64),
    products_price decimal(15,4) NOT NULL,
    products_date_added datetime NOT NULL,
    products_last_modified datetime,
    products_date_available datetime,
    products_weight decimal(5,2) NOT NULL,
    products_status tinyint(1) NOT NULL,
    products_tax_class_id int NOT NULL,
    manufacturers_id int NULL,
    products_ordered int NOT NULL default '0',
    PRIMARY KEY (products_id)
  );
  
  DROP TABLE IF EXISTS products_description;
  CREATE TABLE products_description (
    products_id int NOT NULL,
    language_id int NOT NULL default '1',
    products_name varchar(64) NOT NULL default '',
    products_description text,
    products_url varchar(255) default NULL,
    products_viewed int(5) default '0',
    PRIMARY KEY  (products_id,language_id)
  );
  
  INSERT INTO customers_basket_attributes VALUES (1,1,1,1{4}1{3}5{5}10,4,Memory,1,4 mb);
  INSERT INTO customers_basket_attributes VALUES (2,1,1,1{4}1{3}5{5}10,3,Model,5,Value);
  INSERT INTO customers_basket_attributes VALUES (3,1,1,1{4}1{3}5{5}10,5,Version,10,Download: Windows - English);
  INSERT INTO customers_basket_attributes VALUES (4,1,2,2{4}3{3}6,4,Memory,3,16 mb);
  INSERT INTO customers_basket_attributes VALUES (5,1,2,2{4}3{3}6,3,Model,6,Premium);
  
  INSERT INTO customers_basket VALUES(1,1,1,1{4}1{3}5{5}10,1,0,DATETIME('NOW'));
  INSERT INTO customers_basket VALUES(2,1,2,2{4}3{3}6,2,0,DATETIME('NOW'));
  
  
  INSERT INTO products VALUES (1,32,'MG200MMS','matrox/mg200mms.gif',299.99, DATETIME('NOW'),null,null,23.00,1,1,1,0);
  INSERT INTO products VALUES (2,32,'MG400-32MB','matrox/mg400-32mb.gif',499.99, DATETIME('NOW'),null,null,23.00,1,1,1,0);
  INSERT INTO `products_description` VALUES(1, 1, 'Matrox G200 MMS', '', 'www.matrox.com/mga/products/g200_mms/home.cfm', 0);
  INSERT INTO `products_description` VALUES(2, 1, 'Matrox G400 32MB', '', 'www.matrox.com/mga/products/mill_g400/home.htm', 0);
  </code></pre>
  
  <p><strong>UPDATE:</strong> I just discovered it fails between table <code>customers_basket_attributes</code> and <code>customers_basket</code>.</p>
  
  <p>Table <code>products</code> and <code>products_description</code> work alright if check them with the other 2 tables independently.</p>
  
  <p>** Because of principle, I've decided @Jonathan Leffler deserves the bounty. Not only for the his answer, also for his efforts trying to let me understand his given answer.So please do not try to earn this bounty. If I stay blocked from SO, I want to feel confident in my last action **</p>