I have three tables in the database:
CAR_TAG
CAR_FILE_TAG
CAR_FILE
In the CAR_TAG table, I have tag names, such as:
"sedan", "SUV", "coupe", "convertible", "hatchback", "Toyota", "Ford", "BMW", "Volkswagen", "Honda", "hybrid", "electric", "4x4", "large trunk capacity", "with navigation system"
The CAR_TAG table looks like this, where we have all the tags listed, and each tag has an associated ID.
Example:
| ID | DESCRIPTION_TAG |
|---|---|
| 1 | coupe |
| 2 | with navigation system |
| 3 | sedan |
In the CAR_FILE table, we have various columns with different names, which are not relevant for the current task. For example:
| ID | NAME | SIZE | COLOR |
|---|---|---|---|
| 1 | BMW M3 | 4 | black |
| 2 | Honda Civic 4 | 4 | white |
| 3 | Jaguar | 2 | black |
In the CAR_FILE_TAG table, we have a table that connects when a tag is assigned to a CAR_FILE.
For example, for ID 1 in CAR_FILE, we can assign the tag "with navigation system". In that case, the CAR_FILE_TAG table would have an entry:
| ID | CAR_TAG_ID | CAR_FILE_ID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 1 | 2 |
How to write an SQL query that displays all IDs from the CAR_FILE table that have tags belonging to more than one set from the specified list below.
Car types: sedan, SUV, coupe, convertible, hatchback,
Car brands: Toyota, Ford, BMW, Volkswagen, Honda,
Technical features: hybrid, electric, 4x4, with a large trunk capacity, with navigation system,
So, if a car has a tag that belongs to more than one set, for example, has the tag "sedan" and the tag "hybrid" – tags belonging to two different sets. If it belongs to three sets as well. If tags are only from one set, then we do not include it.
It's a one-to-many relationship in terms of the database architecture; one car can have multiple different tags. Here, I would like to find cars that have tags assigned from more than one set.
Thanks