MySQL Query Assistance -> Multiple WHERE clauses?

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

It would’ve been very easy if the users and colourschemes tables would’ve been normalised.
I guess changing the table structure is out of the question?

Like guido says, restructuring the tables would be the best way in the long run, but in the meantime, the only way I can see is to union the tables together. Something like this:

SELECT u.user_id
     , u.agent_id
     , s.scheme_id
     , u.user_colour_1 AS user_colour
  FROM users u
 INNER JOIN colourschemes ON (u.user_colour_1 = s.colour_1 OR u.user_colour_1 = s.colour_2 OR u.user_colour_1 = s.colour_3 OR u.user_colour_1 = s.colour_4 OR u.user_colour_1 = s.colour_5)
UNION ALL
SELECT u.user_id
     , u.agent_id
     , s.scheme_id
     , u.user_colour_2 AS user_colour
  FROM users u
 INNER JOIN colourschemes ON (u.user_colour_2 = s.colour_1 OR u.user_colour_2 = s.colour_2 OR u.user_colour_2 = s.colour_3 OR u.user_colour_2 = s.colour_4 OR u.user_colour_2 = s.colour_5)
UNION ALL
SELECT u.user_id
     , u.agent_id
     , s.scheme_id
     , u.user_colour_3 AS user_colour
  FROM users u
 INNER JOIN colourschemes ON (u.user_colour_3 = s.colour_1 OR u.user_colour_3 = s.colour_2 OR u.user_colour_3 = s.colour_3 OR u.user_colour_3 = s.colour_4 OR u.user_colour_3 = s.colour_5)

Hi guido, thanks for the reply. I think that would be a bit of a pain as I have done done a lot of work using the current structure. However, it is not completely out of the question. I’d be intrigued by your suggested solution - whether I use it for this one or just learning for the next. Thanks again,

Rik

Hey Dave, thanks very much - a lot for me to digest through there but looking forward to it.

Pete