Mysql – pass selected variable value to inside select query MySQL

MySQL

i have two tables connected with one to many relationship. i have to sort parent table data according to child table data.

tables :

product
---------------------------
|id | name | default_price |
---------------------------

variant
-----------
|id | name| 
-----------


product_stock
------------------------------------------------
|id | product_id | variant_id | price |available 
------------------------------------------------

one product can have many product_stock rows.

I just need to sort product by product_stock minimum price

here what i did:

SELECT id,(SELECT price FROM `product_stock`
             WHERE product_id = 1
             ORDER By price LIMIT 1) as price
    from product ORDER BY price

this is working for only product id 1 . i need to pass dynamically selected product id to it . is this possible? thank you

Best Answer

SELECT product.*
FROM product
JOIN ( SELECT product_id id, MIN(price) price
       FROM product_stock
       GROUP BY 1 ) min_prices USING (id)
ORDER BY min_prices.price

If some products are absent in product_stock they won't be listed. If you need in them then use LEFT JOIN.

Related Solutions

MySql – Get all children in JOIN if one child matches a condition

SELECT p.*, c2.*
FROM Parent p
INNER JOIN Child c 
    ON c.parent_id = p.id
INNER JOIN Child c2
    ON c.parent_id = c2.parent_id
WHERE
    c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'

Mysql – Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key

If you want to use relational principles in database design then certainly not. In a relational design, each entity type in the business domain from the conceptual model is represented by an R-Table in the logical model. The R-Table is a table which, when following a specific discipline, can acquire the properties of a mathematical relational which enable the R-Table to be operated upon with logic statements of arbitrary complexity in an algebraic fashion with guaranteed results. Entity types at the conceptual level share a set of properties called attributes which describe them. One or more of these properties is defined to uniquely identify each entity of the class in order to tell them apart. These are mapped to the R-Table at the logical level as columns, and each Entity mapped to a row. Each row then represents a predicate, and when values for the columns are entered the predicate is instantiated to become a proposition.

A key component at the conceptual level are the business rules which define exactly what attribute values constitute a true proposition. These map to constraints in the logical model, which is a key benefit the relational model provides. When created, the constraints enable the DBMS, which can only manipulate symbols, to effectively keep the data entered consistent with the truth of the real world the data is meant to represent.

So in a nutshell, if you do not declare the constraints - such as a product price must be for one and only one product - to the DBMS, the DBMS will not be able to ensure the data is consistent with the real world. All bets are then off as to correct query results.

Relational design is not easy however. It requires detailed analysis of the business domain to be modeled, a full understanding of the business rules that define consistent data, and a careful mapping to the logical level of R-Tables. All too often what we see in practice is what I call file based design, where tables in the SQL DBMS are used to represent files whose content is based upon totally ad-hoc design considerations. A clue that the 2 tables in question - product and product details - were designed using file based design is in the name of the product details table. A file holds details about something. A Product Details is not an entity type whose class of entities share common properties. This is also evidenced by the fact that every one of those columns in that table are defined as NULL.

If you want pro's and con's of a file based design I am not the right person to elaborate, as those pro's and con's will be ad-hoc and totally based upon your specific circumstances - such as the technology and the work load in play.

A great primer on relational design and its power can be found in Fabian Pascal's Practical Database Foundation Series. Fabian lays out the basics for every day practitioners in easy to understand language. He covers everything I summerized here with clear explanations plus a whole lot more. I highly recommend it.

Best Answer

Related Solutions

MySql – Get all children in JOIN if one child matches a condition

Mysql – Is this proper way to link parent and child tables with logical data instead of parent and child using primary and foreign key

Related Question