StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your table organization is a bit odd, it seems, because you would not normally expect to find both <code>Customers_Basket</code> and <code>Customers_Basket_Attributes</code> containing the <code>products_id</code> column. However, taking that at face value, and using the modern JOIN notation (use that always, not the old SQL-86 join notations), you might be after:</p> <pre><code>SELECT cb.*, cba.*, p.*, pd.* FROM customers_basket AS cb JOIN customers_basket_attributes AS cba ON cb.products_id = cba.products_id JOIN products AS p ON cb.products_id = p.products_id JOIN products_description AS pd ON cb.products_id = pd.products_id WHERE cb.customers_id = "1" </code></pre> <p>However, it much more likely that you need to join Customers_Basket (CB) and Customers_Basket_Attributes (CBA) on other (as yet unidentified) columns, such as <code>CBA.Basket_ID = CB.Basket_ID</code>.</p> <hr> <h3>Schema Analysis</h3> <p><s> Because you've not shown us sufficient of the table schemas, nor have you shown us what you have tried that more or less worked, it is hard to help you more. </s></p> <p>You've now shown us the table schemas, which is a start, but the schema is still somewhat mystifying. In particular, you've not shown the foreign key relationships between the Customers_Basket (CB) and Customers_Basket_Attributes (CBA) tables.</p> <p>You have, it seems:</p> <pre><code>CB CBA customers_basket_id (PK) customers_basket_attributes_id (PK) customers_id customers_id products_id products_id products_unique_id products_unique_id customers_basket_quantity products_options_id final_price products_options_value_id customers_basket_date_added products_options_value_txt </code></pre> <p>This is somewhat contorted.</p> <p>Under what seems to me like a reasonable interpretation of what these might represent, I would expect a customer to have (over time) multiple baskets CB. Each basket can have, potentially, multiple items (maybe one, maybe many). The item information would be in the CBA table. This would suggest a schema such as:</p> <pre><code>CREATE TABLE customers_basket ( customers_basket_id INTEGER PRIMARY KEY ASC, customers_id INTEGER NOT NULL REFERENCES Customers, customers_basket_date_added DATETIME ); CREATE TABLE customers_basket_attributes ( customers_basket_attributes_id INTEGER PRIMARY KEY ASC, customers_basket_id INTEGER NOT NULL REFERENCES Customers_Basket, products_id INTEGER NOT NULL REFERENCES Products, quantity INTEGER NOT NULL ); </code></pre> <p>This leaves most of the product description in the product description tables. It leaves you with a simpler customer basket table - you would probably add other fields to it. And the customers basket attributes table is far simpler too.</p> <p>With this structure, you might write the SQL as:</p> <pre><code>SELECT c.*, b.*, a.*, p.*, d.* FROM customers AS c JOIN customers_basket AS b ON c.customers_id = b.customers_id JOIN customers_basket_attributes AS a ON b.customers_basket_id = a.customers_basket_id JOIN products AS p ON a.products_id = p.products_id JOIN products_description AS p ON p.products_id = d.products_id WHERE c.customers_id = "1" </code></pre> <p>I've not studied your product and product description tables to see how well they work together.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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