how to find the lowest common denominator on one side of a many to many query

Question

I'm trying to extract data from a many to many table configuration.
I'm sorry if this explanation is vague, but it's the best I can do without a lengthy explanation of the whole inner workings of my program.

I have 2 modules in an application.
Module one shows information relevant to a group of items.
And it strips out information that is not present in all items. (lowest common denominator)

So...
Item one is red and green.
Item two is green and blue.
And item three is just green.

So table one would be the list of items and table two would be the list of colors.
And of course, a third table holds the relationships.

I'm trying to get the module to only show elements from table two that are present in all items from table one.

In this case, it would strip the shown elements to just green because that's the only common element.

I tried doing this programatically and it was a nightmare to look at and debug. That and this will be a mobile app so all the for loops will really lag my program. I know that there has to be a way to do it with SQL, but after several hours researching, I still can't seem to wrap my head around how.

Thanks.

UPDATE for clarity:

OK, I made 2 mistakes in my question. First off, I'm not looking for common quantity, I'm looking for common VALUE. So I only want colors that exist in all three items and to discard the rest. Second I should have been more specific. I'm developing an app for the Android phones, so I'm using Java and SQLite.

Here's what I have so far in the original form, not the color example I used. In this code tag would equal color and other would equal item...

SELECT DISTINCT tag.name
FROM other_tag_relationship AS otr
JOIN tag ON otr.tag_id = tag._id
WHERE otr.other_id in (2, 3, 4);

This obviously returns a distinct list of all tags for all of the given others.

What I want to do now is show only the tags that are available in ALL others and discard the rest. Like remove any tags from 2 that aren't also in 3 and 4 for example. Also, the list (2, 3, 4) would be generated dynamically at run time.

Thanks again.

Answer 1

What you describe sounds a lot like an INNER JOIN but without seeing your layout it'll be real hard to tell.

Answer 2

From your description, it sounds like you want a list of Colors (Table 2) that are associated with all items (Table 1) via the middle table 3 (ItemColors):

Select IC.ColorId, C.Name
From ItemColors As IC
    Join Colors As C
        On C.Id = IC.ColorId
Group By IC.ColorId, C.Name
Having Count(*) =   (
                    Select Count(*) 
                    From Items As I2 
                    )

Answer 3

I tried doing this programatically and it was a nightmare to look at and debug. That and this will be a mobile app so all the for loops will really lag my program.

Then you're doing it wrong. You only need one for loop and a set intersection function. For example, in Python:

import functools
import operator

def common_colors(query_results):
    map_item_to_color = {}
    for (item, color) in query_results:
        if item in map_item_to_color:
            map_item_to_color[item].add(color)
        else:
            map_item_to_color[item] = {color}
    return functools.reduce(operator.and_, map_item_to_color.values())

# SELECT Item, Color FROM YourTables
DATA = [(1, 'red'), (1, 'green'),
        (2, 'green'), (2, 'blue'),
        (3, 'green')]
print(common_colors(DATA)) # {'green'}

An SQL equivalent is

SELECT Color FROM ItemColors GROUP BY Color HAVING
   COUNT(Color) = (SELECT COUNT(DISTINCT Item) FROM ItemColors);

Answer 4

SELECT DISTINCT name
FROM (
    SELECT t1.name FROM other_tag_relationship AS otr
        JOIN tag AS t2 ON otr.tag_id = t2._id AND t2.other_id = 2
    UNION ALL
    SELECT t3.name FROM other_tag_relationship AS otr
        JOIN tag AS t3 ON otr.tag_id = t3._id AND t3.other_id = 3
    UNION ALL
    SELECT t4.name FROM other_tag_relationship AS otr
        JOIN tag AS t4 ON otr.tag_id = t4._id AND t4.other_id = 4
)