Databases
StackOverflow2013
Postsdbo
POHow do I select rows from a MySQL table grouped by one column with required values in another
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I select rows from a MySQL table grouped by one column with required values in another
    primarykey
    data
    text
    <p>I need to filter products where certain attributes are stored in a joined table that match all the required properties, i.e. users need to be able to gradually narrow down their search by adding requirements. </p> <p>The problem really just concerns the properties table I think, rather than the join, given the following (simplified) table of product properties:</p> <pre> id product_id property value --------------------------------- 1 1 color red 2 1 size small 3 2 color red 4 2 size large </pre> <p>how would I get all the product_ids where value is both 'red' and 'small' ?</p> <p>A similar question <a href="https://stackoverflow.com/questions/329822/mysql-strict-select-of-rows-involving-many-to-many-tables">was asked</a> before but not answered very fully. A solution involves a COUNT and HAVING to get the rows where there are as many rows in each group as required values e.g.</p> <pre><code>SELECT product_id, count(*) AS group_count FROM properties where value = 'red' OR value = 'small' GROUP BY product_id HAVING group_count = 2 </code></pre> <p>This works but I'm concerned about performance, it seems like there would be a better way.</p> <p>Eventually this would need to be joined with, or at least used to filter the products table:</p> <pre> id name ------------- 1 Product 1 2 Product 2 </pre> <p>I forgot to mention that I have 2 of these properties tables joined to products that I need to filter on, one with regular attributes of a product, another with available configurable options (a bit like variants). The scenario is to allow users to filter products like: "show products where gender = 'male', brand = 'nike' and size == 'small'" where gender and brand are 'properties' and size is in options (configurable when adding to cart)</p> <p>The solution of using a group with a count works with the 2 joined tables still but it gets messy, the required group count is the number of required options on the first table multiplied by the number on the second.</p> <p>I could just fetch the ids from properties (and the other table) then just do a select where id IN(ids), matching a set of ids for both property tables, I don't like the idea of doing this with a really long list of ids though.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USDavidNorth
    plurals
    1. PL
      singulars
      1. LTLinked
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I select rows from a MySQL table grouped by one column with required values in another
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I select rows from a MySQL table grouped by one column with required values in another
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat happens when group_count is greater than 2 in your example? They'll be excluded. I think you should have it as HAVING group_count > 0
      singulars
      1. POHow do I select rows from a MySQL table grouped by one column with required values in another
      1. USEppz
 

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