Common set of values for multiple columns

[malcolm.reed]

Good Afternoon,
I'm hoping someone can put me on the right track with this little problem. I've created a simple database that I've been playing around with but this is a technique I've wrestled with on real projects. I struggle to write SQL by hand, so try to use the Query Designer wherever I can (maybe that's my problem). Or it could be a basic design problem. Either way, I'm happy to take advice.

In my example, I have a form where users can pick their name from a list (names are in their own table), then choose their top2 favourite colours from two more drop down boxes. The list of colours is also in its own table but there is just one colour list used to populate both controls on the form.

What I want is a query which returns 3 columns and in this case 10 rows - the real name, colour 1 and colour 2.

e.g.
Bill, yellow, red
fred, red, green
john,indigo,yellow

and so on.

I can perfectly easily create individual queries for either colour - but not both. I won't list all the ways it's gone wrong for me but if you look at the attachment you'll see some of my failed experiments.

All (polite!) suggestions welcome - thanks!

 

[Taruz]

Hi..

Try this query..:

select  
         Firstname, 
         trz.Colour as fav1, 
         tbl_colours.Colour as fav2

from tbl_colours inner join  (
                                                tbl_favourites inner join (
                                                               select 
                                                                           person, 
                                                                           tbl_People.Firstname, 
                                                                           tbl_colours.Colour, 
                                                                           tbl_colours.ID
                                                                from (tbl_People inner join tbl_favourites on tbl_People.ID = tbl_favourites.Person) 
                                                                          inner join tbl_colours on tbl_favourites.Colour = tbl_colours.ID)  as trz 
                             on tbl_favourites.Person = trz.person) on tbl_colours.ID = tbl_favourites.Colour2

 

[malcolm.reed]

Hi
Thanks very much - the query works!!
I'll try to study it over the weekend and understand what you've done.
It looks quite complicated: would it be easier if I had designed the database in a different way? Also, can you tell me what sort of approach did you follow, to produce this query? Maybe you're just good with SQL!!

Anyway, thanks again
Malcolm

 

[Taruz]

Hi
Thanks very much - the query works!!
I'll try to study it over the weekend and understand what you've done.
It looks quite complicated: would it be easier if I had designed the database in a different way? Also, can you tell me what sort of approach did you follow, to produce this query? Maybe you're just good with SQL!!

Anyway, thanks again
Malcolm

Hi..

If you look carefully, before the first color of the staff, I created a sub-query matches with the color chart..

then matched with the color chart again this query.. The first color in the query, since there are not hard to get other colors.


Also .. I like SQL