StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORetrieve values from multiple tables relationed
text
Body
copied!<p>So, I have a table named <code>clients</code>, another one known as <code>orders</code> and other two, <code>orders_type_a</code> and <code>orders_type_b</code>.</p> <p>What I'm trying to do is create a query that returns the list of all clients, and for each client it must return the number of orders based on this client's id and the amount of money this customer already spent.</p> <p>And... I have no idea how to do that. I know the logic behind this, but can't find out how to <em>translate</em> it into a MySQL query.</p> <p>I have a basic-to-<em>thinkimgoodbutimnot</em> knowledge of MySQL, but to this situation I've got really confused.</p> <p>Here is a image to illustrate better the process I'm trying to do: <img src="https://i.stack.imgur.com/ZsCOW.png" alt="The logic behind the query"></p> <p>Useful extra information:</p> <ul> <li>Each <code>orders</code> row have only one <code>type</code> (which is <code>A</code> or <code>B</code>)</li> <li>Each <code>orders</code> row can have multiple <code>orders_type_X</code> (where X is <code>A</code> or <code>B</code>)</li> <li>orders relate with client through the column <code>client_id</code></li> <li><code>orders_type_X</code> relate with <code>orders</code> through the column <code>order_id</code></li> </ul> <p>This process is being made today by doing a query to retrieve clients, and then from each entry returned the code do another query (with php) to retrieve the orders and yet another one to retrieve the values. So basically for each row returned from the first query there is two others inside it. Needless to say that this is a horrible approach, the performance sucks and I thats the reason why I want to change it.</p> <hr> <p><strong>UPDATE width tables columns:</strong></p> <p><strong>clients:</strong></p> <p><code>id | name | phone</code></p> <p><strong>orders:</strong></p> <p><code>id | client_id | date</code></p> <p><strong>orders_type_a:</strong></p> <p><code>id | order_id | number_of_items | price_of_single_item</code></p> <p><strong>orders_type_b:</strong></p> <p><code>id | order_id | number_of_shoes_11 | number_of_shoes_12 | number_of_shoes_13 | price_of_single_shoe</code></p> <p>For any extra info needed, just ask.</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