A few months ago I asked this question: MySQL select multiple id at the same time with optional id with php
Today I have to complicate the query and search with:
- required id
- alternative mandatory ids
- optional ids
I have a table called skills that contains all the data, an example, where:
- skillType = 0 when skill is required;
- skillType = 1 when skill is a required alternative
- skillType = 2 when skill is a optional
| id | idUser | idSkill | skillType | optionalSkill |
|---|---|---|---|---|
| 1 | 1 | 1 | 0 | 0 |
| 2 | 2 | 4 | 0 | 0 |
| 3 | 2 | 7 | 0 | 0 |
| 4 | 2 | 8 | 1 | 7 |
| 5 | 3 | 11 | 0 | 0 |
| 6 | 4 | 8 | 0 | 0 |
| 7 | 4 | 11 | 0 | 0 |
| 8 | 4 | 14 | 1 | 11 |
| 9 | 4 | 16 | 2 | 0 |
| 10 | 6 | 1 | 0 | 0 |
I would like to do 2 searches:
- Basic search: all mandatory and alternative ids
- Premium search: all mandatory and alternative id's and optional id's.
In my case:
- results is idUser 2 if I search for users with idSkill 4 and idSkill (7 or 8)
- results is idUser 4 if I search for users with idSkill 8 and idSkill (11 or 14) and optional idSkill 16.
I don't know if this system can work, or if it requires a different database design.
Can you give me some advice?
