sql - MySQL Query Assistance -> Multiple WHERE clauses? -

sql - MySQL Query Assistance -> Multiple WHERE clauses? -

i have database tables shown below.

what looking records of colourschemes colour_1, colour_2, colour_3, colour_4, colour_5 matches of users' user_colour_1, user_colour_2 or user_colour_3 entries agent_id.

basically user signed agent , dictates colours like. colourschemes (a business user) sign on own , dictates colours cater for. need find, particular agent, whether any colourschemes' colours catered any of users colours chose.

---------------------- 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 | reddish 2 | yellowish 3 | bluish 4 | greenish 5 | orange 6 | violet 7 | black

ok , sorry long here wanted results...

------------------------------------------ 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

what complex clause this.

select u.agent_id, u.agent_name users u, colourschemes c u.user_colour_1 = c.scheme_id or u.user_colour_2 = c.scheme_id or u.user_colour_3 = c.scheme_id , c.colour_1 = "blue" , c.colour_2 = "purple" , c.colour_3 = "black" , c.colour_4 = "yellow" , c.colour_5 = "green" ;

mysql sql