I’ve been landed with a quick MySQL task as a colleague who would normally do this has selfishly gone on holiday and was wondering if anyone could help out.
The query sounds simple but I’m afraid queries aren’t my skill! I have the database tables as shown below. What I require is…
What I am looking to do is get all records of colourschemes where colour_1, colour_2, colour_3, colour_4, colour_5 matches any of users’ user_colour_1, user_colour_2 or user_colour_3 entries by agent_id.
Basically a user is signed up by an agent and then dictates what colours they like. The colourschemes (a business user) sign up on their own and dictates what colours they cater for. I need to find, for a particular agent, whether any colourschemes’ colours are catered for any of the users colours they chose.
Any advice or tips would be greatly appreciated.
Regards,
Rik
----------------------
agents
----------------------
agent_id | agent_name
--------- | ----------
1 | Rod
2 | Jane
3 | Freddy
------------------------------------------------------------------------------
users
------------------------------------------------------------------------------
user_id | agent_id | user_name | user_colour_1 | user_colour_2 | user_colour_3
------- | -------- | --------- | ------------- | ------------- | -------------
1 | 1 | Andy | 1 | 2 | 3
2 | 2 | Betty | 2 | 1 | 5
3 | 3 | Claire | 3 | 1 | 6
4 | 1 | Derek | 4 | 5 | 7
5 | 1 | Eric | 1 | 6 | 2
6 | 2 | Fay | 2 | 7 | 3
7 | 3 | Gary | 3 | 1 | 7
------------------------------------------------------------------------------
----------------------------------------------------------------
colourschemes
----------------------------------------------------------------
scheme_id | colour_1 | colour_2 | colour_3 | colour_4 | colour_5
--------- | -------- | -------- | -------- | -------- | --------
1 | 1 | 2 | 3 | 5 | 7
2 | 2 | 3 | 5 | 1 | 4
3 | 3 | 6 | 7 | 2 | 4
4 | 4 | 3 | 1 | 7 | 2
5 | 5 | 6 | 7 | 2 | 3
6 | 6 | 5 | 1 | 4 | 3
7 | 7 | 3 | 6 | 1 | 2
-----------------------
colours
-----------------------
colour_id | colour_name
--------- | -----------
1 | red
2 | yellow
3 | blue
4 | green
5 | orange
6 | purple
7 | black
And sorry, it’s so long but these are the results I would after…
------------------------------------------
results table
------------------------------------------
agent_id | user_id | scheme_id | colour_id
------------------------------------------
1 | 1 | 1 | 1
1 | 1 | 1 | 2
1 | 1 | 1 | 3
1 | 1 | 2 | 2
1 | 1 | 2 | 1
1 | 1 | 2 | 3
1 | 1 | 3 | 3
1 | 1 | 3 | 2
1 | 1 | 4 | 3
1 | 1 | 4 | 1
1 | 1 | 4 | 2
1 | 1 | 5 | 2
1 | 1 | 5 | 3
1 | 1 | 6 | 1
1 | 1 | 6 | 3
1 | 1 | 7 | 3
1 | 1 | 7 | 1
1 | 1 | 7 | 2
1 | 4 | 1 | 5
1 | 4 | 1 | 7
1 | 4 | 2 | 5
1 | 4 | 2 | 4
1 | 4 | 3 | 7
1 | 4 | 3 | 4
1 | 4 | 4 | 4
1 | 4 | 4 | 7
1 | 4 | 5 | 5
1 | 4 | 5 | 4
1 | 4 | 6 | 5
1 | 4 | 6 | 4
1 | 4 | 7 | 7
1 | 5 | 1 | 1
1 | 5 | 1 | 2
1 | 5 | 2 | 2
1 | 5 | 2 | 1
1 | 5 | 3 | 6
1 | 5 | 3 | 2
1 | 5 | 4 | 6
1 | 5 | 4 | 2
1 | 5 | 5 | 6
1 | 5 | 5 | 1
1 | 5 | 6 | 1
1 | 5 | 6 | 2
1 | 5 | 7 | 6
1 | 5 | 7 | 1
1 | 5 | 7 | 2