How to find the lowest common denominator on the one hand from many to many queries

Question

I am trying to extract data from many tables. I apologize if this explanation is fuzzy, but this is the best I can do without a detailed explanation of all the internal workings of my program.

I have 2 modules in the application.
The first module displays information related to a group of elements.
And it removes information that is absent in all elements. (lowest common denominator)

So...
The first item is red and green.
The second item is green and blue.
And point three is only green.

Thus, the table will be a list of elements, and the second table will be a list of colors.
And of course, the third table contains relationships.

I am trying to get the module to display only the elements from table 2 that are present in all elements from the first.

In this case, this will cause the displayed items to be simply green, because this is the only common item.

I tried to do this programmatically, and it was a nightmare to watch and debug. This will be a mobile application, so all for loops will lag far behind my program. I know there must be a way to do this using SQL, but after hours of research, I still can't wrap myself around it.

Thanks.

UPDATE for clarity:

Ok, I made 2 mistakes in my question. Firstly, I am not looking for a total, I am looking for a total VALUE. Therefore, I want only the colors that exist in all three elements, and abandon the rest. Secondly, I had to be more specific. I am developing an application for Android phones, so I use Java and SQLite.

Here is what I have so far in the original form, and not in the color example that I used. This code tag will have the same color and the other will be equal to the element ...

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 explicitly returns a separate list of all tags for all others.

Now I want to show only those tags that are available in ALL others, and abandon the rest. How to remove any tags from 2 that are also not in 3 and 4. In addition, a list (2, 3, 4) will be generated dynamically at run time.

Thanks again.

Answer 1

From your description, it seems that you need a list of colors (Table 2) that are associated with all the elements (Table 1) through 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 2

What you describe sounds the same as INNER JOIN , but without seeing your layout, it will be hard to say.

Answer 3

I tried to do this programmatically and it was a nightmare to watch and debug. This will be a mobile application, so that all the loops will really lag behind my program.

Then you do it wrong. You only need one for loop and a set of intersection functions. 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'} 

SQL equivalent

 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 )