MYSQL select query based on another tables multiple values

Question

I have two tables one for units and one for Amenities

Table units
+---------+---------------------+---------------------+-----------+----------+
| unit_id |     date added      |     date modified   | unit name |   user   |
+---------+---------------------+---------------------+-----------+----------+
|       1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 |  Villa 1  | Smith    |
|       2 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 |  Villa 2  | Smith    |
|       3 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 |  Villa 3  | Jones    |
|       4 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Apartment | Smith    |
+---------+---------------------+---------------------+-----------+----------+

Table Amenities       
+---------+-----------+-------------------+
|      id |  Unit_id  |   Amenity         |
+---------+-----------+-------------------+
|       1 |         1 |   Air conditions  |
|       2 |         1 |   Internet        |
|       3 |         1 |   Water heaters   |
|       4 |         1 |   TV              |
|       5 |         2 |   TV              |
|       6 |         2 |   pool            |
|       7 |         2 |   Internet        |
|       8 |         3 |   Internet        |
|       9 |         4 |   Internet        |
+---------+-----------+-------------------+

i want to select units where it has both TV and Internet

I try

select units.* from units left join Amenities  on units.unit_id=Amenities.Unit_id  

where Amenities.Amenity='TV' and  Amenities.Amenity='Internet'

but not work

do OR there, what you want exactly? a unit with both the Amenity or one of the both? — Ritesh Khatri
– Ritesh Khatri, Commented Nov 13, 2017 at 11:22
@Wodin - yes - or if you want to find units that have 2 out of 3. — Strawberry
– Strawberry, Commented Nov 13, 2017 at 12:06

Strawberry · Accepted Answer · 2017-11-13 11:43:09Z

1

DROP TABLE IF EXISTS amenities;

CREATE TABLE amenities
(unit_id INT NOT NULL
,amenity VARCHAR(50) NOT NULL
,PRIMARY KEY(unit_id,amenity)
);

INSERT INTO amenities VALUES
(1,'Air conditions'),
(1,'Internet'),
(1,'Water heaters'),
(1,'TV'),
(2,'TV'),
(2,'pool'),
(2,'Internet'),
(3,'Internet'),
(4,'Internet');

SELECT unit_id 
  FROM amenities 
 WHERE amenity IN ('TV','Internet') 
 GROUP 
    BY unit_id 
HAVING COUNT(*) = 2;
+---------+
| unit_id |
+---------+
|       1 |
|       2 |
+---------+

Obviously, you'd never have a table like the one you describe. Instead, you'd have a table of unit, a table of amenities, and a table which says which amenity belongs to which unit.

answered Nov 13, 2017 at 11:43

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

2 Comments

Wodin Over a year ago

I agree that it's better to have an Amenties table that only has amenity_id and amenity columns and then a linking table with unit_id and amenity_id. Then Amenities would only have one record for e.g. Internet, but there would be a record in unit_amenities for each unit with the Internet amenity.

Strawberry Over a year ago

@Wodin Exactly.

Wodin · Accepted Answer · 2017-11-13 11:42:44Z

0

SELECT ua.*
FROM units AS ua
INNER JOIN units AS ub ON ua.unit_id = ub.unit_id
INNER JOIN Amenities AS aa
    ON ua.unit_id = aa.Unit_id
    AND aa.Amenity = 'TV'
INNER JOIN Amenities AS ab
    ON ub.unit_id = ab.Unit_id
    AND ab.Amenity = 'Internet';

Here's the output generated with your data:

+---------+---------------------+---------------------+-----------+-------+
| unit_id | date added          | date modified       | unit name | user  |
+---------+---------------------+---------------------+-----------+-------+
|       1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Villa 1   | Smith |
|       2 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Villa 2   | Smith |
+---------+---------------------+---------------------+-----------+-------+

edited Nov 13, 2017 at 11:42

answered Nov 13, 2017 at 11:37

Wodin

3,5682 gold badges29 silver badges59 bronze badges

1 Comment

mahmoud ahmed Over a year ago

Thanks for helping me, i found that his answer is petter

Collectives™ on Stack Overflow

MYSQL select query based on another tables multiple values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related