Select entries bases on multiple entries in another table MySQL

I have three tables kinda like the example below:

TABLE CARS

carId   carName
-----   --------
1       a
2       a
3       b
4       b
5       b
6       c
7       d
8       e
9       f
10      g

TABLE CAR NAMES

nameId  carName
------  -------
1       a
2       b
3       c
4       d
5       e
6       f
7       g

TABLE CAR ATTRIBUTES

nameId  attribute
------  ---------
1       FAST
1       SMALL
1       SHI开发者_如何学运维NY
2       BIG
2       SLOW
3       EXPENSIVE
4       SHINY
5       FAST
5       SMALL
6       FAST
7       SMALL

I have been trying to put together a query to get the carId where it has attributes of both FAST and SMALL, but have been having no luck. What is the best way to do this?

In this example the result would be carID 1 and carName a where attribute = SMALL and attribute = FAST

Looking for something like this? (untested)

select a.carId
  from cars        a
  join car_names   b using(carName)
  join car_attribs c using(nameId)
 where c.attribute in('SMALL', 'FAST')
 group 
    by a.carId
having count(distinct c.attribute) = 2;

This is not to be considered an exhaustive answer, but just a few points on the topic.

Since the question is tagged with the [mysql] tag, let me say that, in general, relational databases aren't particularly suitable for storing data using the EAV model. You can still design an EAV model in SQL, but you will have to sacrifice many advantages that a relational database would give. Not only you won't be able to enforce referential integrity, use SQL data types for values and enforce mandatory attributes, but even the very basic queries (like this one) can become difficult to write. In fact, to overcome this limitation, several EAV solutions rely on data duplication, instead of joining with related tables, which as you can imagine, has plenty of drawbacks.

If you really require a schemaless design, you could consider using a NoSQL solution. Even though the weaknesses of EAV relative to relational databases also apply to NoSQL alternatives, you will be offered additional features that are difficult to achieve with conventional SQL databases. For example, usually NoSQL datastores can be scaled much easier than relational databases, simply because they were designed to solve some sort of scalability problem, and they intentionally dropped features that make scaling difficult.

On the other hand, @Ronnis' solution should work, and in general, using COUNT() is one common technique to solve your problem.

Test case:

CREATE TABLE `cars` (carId int, carName char(1));
CREATE TABLE `car_names` (nameId int, carName char(1));
CREATE TABLE `car_attributes` (nameId int, attribute varchar(40));

INSERT INTO `cars` VALUES (1, 'a');
INSERT INTO `cars` VALUES (2, 'a');
INSERT INTO `cars` VALUES (3, 'b');
INSERT INTO `cars` VALUES (4, 'b');
INSERT INTO `cars` VALUES (5, 'b');
INSERT INTO `cars` VALUES (6, 'c');
INSERT INTO `cars` VALUES (7, 'd');
INSERT INTO `cars` VALUES (8, 'e');
INSERT INTO `cars` VALUES (9, 'f');
INSERT INTO `cars` VALUES (10, 'g');


INSERT INTO `car_names` VALUES (1, 'a');
INSERT INTO `car_names` VALUES (2, 'b');
INSERT INTO `car_names` VALUES (3, 'c');
INSERT INTO `car_names` VALUES (4, 'd');
INSERT INTO `car_names` VALUES (5, 'e');
INSERT INTO `car_names` VALUES (6, 'f');
INSERT INTO `car_names` VALUES (7, 'g');


INSERT INTO `car_attributes` VALUES (1, 'FAST');
INSERT INTO `car_attributes` VALUES (1, 'SMALL');
INSERT INTO `car_attributes` VALUES (1, 'SHINY');
INSERT INTO `car_attributes` VALUES (2, 'BIG');
INSERT INTO `car_attributes` VALUES (2, 'SLOW');
INSERT INTO `car_attributes` VALUES (3, 'EXPENSIVE');
INSERT INTO `car_attributes` VALUES (4, 'SHINY');
INSERT INTO `car_attributes` VALUES (5, 'FAST');
INSERT INTO `car_attributes` VALUES (5, 'SMALL');
INSERT INTO `car_attributes` VALUES (6, 'FAST');
INSERT INTO `car_attributes` VALUES (7, 'SMALL');

Result:

SELECT    a.carId
FROM      cars a
JOIN      car_names b USING(carName)
JOIN      car_attributes c USING(nameId)
WHERE     c.attribute IN('SMALL', 'FAST')
GROUP BY  a.carId
HAVING    COUNT(distinct c.attribute) = 2;

+-------+
| carId |
+-------+
|     1 |
|     2 |
|     8 |
+-------+
3 rows in set (0.00 sec)