MySQL: Select an item that matches multiple rows

Question

I have a few (over 20) tables in MySQL, which describe an item on many different levels (colors, location etc.). The following is an example of "table01"

+----+----------+
| ID | property |
+----+----------+
|  1 | A        |
|  1 | B        |
|  2 | C        |
|  2 | B        |
+----+----------+

Now I want to search for items that match multiple criteria. The following query works just fine

SELECT
  table01.ID, table01.property, table02.property, table03.property [...]
FROM
  table01
LEFT JOIN table02 ON table02.ID = table01.ID
LEFT JOIN table03 ON table03.ID = table01.ID
[...]
WHERE
  table01.property = "A"
  and table02.property = "B"
  and table03.property = "A"
  [...]

Heres my problem. I want to search for an item that matches for a few properties in one table. For example (this query obviously does not work)

table01.property = "A" AND table01.property = "B"

I don't know how to achieve that, because the information is stored in multiple rows.

Any suggestions? The database is huge (a few thousand entries per table) and new rows get added from time to time. Should I do some of the processing through PHP or is there a pure MySQL Solution?

Question need more info. Are the queried properties static? Or you want to create dynamic queries to user defined properties? — Salvador Ruiz Guevara
– Salvador Ruiz Guevara, Commented Oct 21, 2017 at 18:26
The database structure is static, the tables and their colum(names) stay the same. Items and properties are added from time to time — Jan Cieslik
– Jan Cieslik, Commented Oct 21, 2017 at 18:32
what i meant is the query itself static? Do you query the same properties all the time? — Salvador Ruiz Guevara
– Salvador Ruiz Guevara, Commented Oct 21, 2017 at 18:48
The query itself changes all the time, the count of the queried properties and their values changes — Jan Cieslik
– Jan Cieslik, Commented Oct 21, 2017 at 18:54

Gnudiff · Accepted Answer · 2017-10-21 18:36:21Z

2

You could achieve this, for example, by doing

SELECT ID,count(property) AS CNT FROM table01 
WHERE property = 'A' OR  property = 'B'
GROUP BY ID
HAVING CNT=2;

This will give you list of IDs who have both properties.

However, it can grow more convoluted with more properties to check for AND unwieldy with more tables. If at all possible, it might be more useful to rethink your database schema to at least have a single table with properties, not multiple.

answered Oct 21, 2017 at 18:36

Gnudiff

4,3251 gold badge26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harshil Doshi · Accepted Answer · 2017-10-21 19:59:54Z

0

You can use query given by @Gnudiff with your query as follows:

SELECT
  table01.ID, table01.property, table02.property, table03.property [...]
FROM
  (SELECT *FROM table01 
   WHERE property = 'A' OR  property = 'B'
   GROUP BY ID
   HAVING count(property)=2) as table01
LEFT JOIN table02 ON table02.ID = table01.ID
LEFT JOIN table03 ON table03.ID = table01.ID
[...]
WHERE
  table02.property = "B"
  and table03.property = "A"
  [...]

Click here for Demo

Hope it helps!

edited Oct 21, 2017 at 19:59

answered Oct 21, 2017 at 19:42

Harshil Doshi

3,6123 gold badges18 silver badges41 bronze badges

Collectives™ on Stack Overflow

MySQL: Select an item that matches multiple rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related