SQL problems and solutions (part 1)

Tram Ho

In this series, I will post all the articles that I and my colleagues encounter. it is the requirements encountered in the working process, it is not too difficult. I note it to share and save for myself:

1. Requirements

I have 2 tables like this: services

firstservice 1
2service 2
3service 3

room_services Value in the table


Each relationship is as follows: 1 room has many services , each service can be performed in many rooms

Problem requirement: Find out the id of all the rooms that perform all the services : As above, I will find room 1 (id = 1) it performs all 3 services (1,2,3 )

To better understand the problem, we should proactively create data, try to read the test request then read it more effectively.

2. Analysis:

  • Based on the services table, we get a list of all service_id

  • Based on the table room_services , when grouped by service_id , we will group the service groups according to room_id, the service_id can only be obtained based on the aggrate function operator. list Id from the room table

3. Solution

Method 1 : Based on the sum operator

Method 2 : If the field service_id is not an integer, then we can use the group_concat function to create strings , pay attention to the order, then we need to sort first.

If you need to talk, please comment below. Thanks for reading my post.

Share the news now

Source : Viblo