Listing common values from Many to Many tables

Question

May I ask for your help with a Many to Many query please ?

I have an ACE db built with MS Access 2010. It has the following tables.

Flags
flagID      flagName                country
1           Stars and Stripes       USA
2           French Tricolour        France
3           The Yellow and Blue     Sweden
4           Union Jack              UK
5           Jolly Roger             Pirates
6           Trinacria               Sicily

Colours
colourID    colour
1           Red
2           Blue
3           White
4           Black
5           Yellow

Juntion
JID         fkFlags     fkColours
For brevity, I’ll omit the field values from the Junction table.

If I query the tables with

SELECT Flags.flagName, Flags.country, Colours.colour
FROM Flags INNER JOIN
                 (Colours INNER JOIN Juntion ON Colours.colourID = Juntion.fkColours)
                 ON Flags.flagID = Juntion.fkFlags
WHERE Flags.flagName = 'Jolly Roger' OR Flags.flagName = 'Stars and Stripes';

My results are what I would expect.

flagName            country     colour                                       
Stars and Stripes   USA         Red                         
Stars and Stripes   USA         White                       
Stars and Stripes   USA         Blue                        
Jolly Roger         Pirate      Black                       
Jolly Roger         Pirate      White    

Similarly, if I query the tables with

SELECT Flags.flagName, Flags.country, Colours.colour
FROM Flags INNER JOIN (Colours INNER JOIN Juntion ON Colours.colourID =   Juntion.fkColours) 
ON Flags.flagID = Juntion.fkFlags
WHERE Colours.Colour IN ((
                      SELECT C.colour
                      FROM Flags INNER JOIN (Colours C INNER JOIN Juntion ON C.colourID = Juntion.fkColours)
                      ON Flags.flagID = Juntion.fkFlags
                      WHERE (c.Colour = 'White')
                      ))
OR Colours.Colour IN ((
                      SELECT C.colour
                      FROM Flags INNER JOIN (Colours C INNER JOIN Juntion ON C.colourID = Juntion.fkColours)
                      ON Flags.flagID = Juntion.fkFlags
                      WHERE (c.Colour = 'Blue')
                      ))

My results are what I would expect.

flagName            country colour
Stars and Stripes   USA     White                       
Stars and Stripes   USA     Blue                        
The Yellow and Blue Sweden  Blue                        
Jolly Roger         Pirate  White                       
French Tricolor     France  Blue                        
French Tricolor     France  White                       
Union Jack          UK      White                       
Union Jack          UK      Blue

However, what I would really like is to get results for all flagNames where colour = Red, White AND Blue.

Stars and Stripes
French Tricolour
Union Jack

Also, given the flagNames of 'Stars and Stripes' and 'Jolly Roger', what colours do they share ?

White

In the last query example above, if I change the OR to AND, no results are returned at all. May I ask for your help writing those queries please ?

Thanks for your time.

Answer 1

For flags with all three colours:

SELECT flagName
FROM Flags
WHERE
    flagID IN
            (
                SELECT Juntion.fkFlags
                FROM Juntion INNER JOIN Colours ON Juntion.fkColours = Colours.colourID
                WHERE Colours.colour = "Red"
            )
        AND flagID IN
                (
                    SELECT Juntion.fkFlags
                    FROM Juntion INNER JOIN Colours ON Juntion.fkColours = Colours.colourID
                    WHERE Colours.colour = "White"
                )
        AND flagID IN
                (
                    SELECT Juntion.fkFlags
                    FROM Juntion INNER JOIN Colours ON Juntion.fkColours = Colours.colourID
                    WHERE Colours.colour = "Blue"
                )

Similarly, for common colours between two flags

SELECT colour
FROM Colours
WHERE
    colourID IN
            (
                SELECT Juntion.fkColours
                FROM Juntion INNER JOIN Flags ON Juntion.fkFlags = Flags.flagID
                WHERE Flags.flagName = "Stars and Stripes"
            )
        AND colourID IN
                (
                    SELECT Juntion.fkColours
                    FROM Juntion INNER JOIN Flags ON Juntion.fkFlags = Flags.flagID
                    WHERE Flags.flagName = "Jolly Roger"
                )