Databases
StackOverflow2013
Postsdbo
POJoining two tables in a SELECT query is not working
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoining two tables in a SELECT query is not working
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLuke Chadwick
    1. USwHiTeHaT
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJoining two tables in a SELECT query is not working
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POJoining two tables in a SELECT query is not working
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POJoining two tables in a SELECT query is not working
      1. This table or related slice is empty.
      1. VTDownMod
    1. COwhy don't you first explain what are you trying to accomplish? maybe also the relevant part of your schema.
      singulars
      1. POJoining two tables in a SELECT query is not working
      1. USmilan
    2. COi don't agree with you, it's hard to tell what are you trying to do. anyway, good luck.
      singulars
      1. POJoining two tables in a SELECT query is not working
      1. USmilan
    3. COSuggest you post example data and desired results.
      singulars
      1. POJoining two tables in a SELECT query is not working
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload