I am sure there's a solution gift for this but bear beside me as I am a new comer to SQL and am unsure how you can request the question.
I've data such as this (this really is shorthand purely for instance). This really is inside a postgres db.
table1 id value 1 111 1 112 1 113 2 111 2 112 2 116 3 111 3 122 3 123 4 126 5 123 5 125 6 111 6 112 6 116 table2 value 111 112 116
I want return the id of table1 where all values in table2 appear in the values of table1. So let's imagine, my query would return 2 and 6.
Can there be any method of doing this in SQL? Or would you possibly guide me to some data structure that will permit me to obtain this result? I can alter the dwelling of either table to support the best necessity of getting this result
Many thanks. An response to this is a existence saving idea.
Think about this demo:
CREATE TEMP TABLE table1(id int, value int); INSERT INTO table1 VALUES (1,111),(1,112),(1,113) ,(2,111),(2,112),(2,116) ,(3,111),(3,122),(3,123) ,(4,126) ,(5,123),(5,125) ,(6,111),(6,112),(6,116); CREATE TEMP TABLE table2(value int); INSERT INTO table2 VALUES (111) ,(112) ,(116); SELECT t1.id FROM table1 t1 JOIN table2 t2 USING (value) GROUP BY t1.id HAVING count(*) = (SELECT count(*) FROM table2) ORDER BY t1.id;
id ----- 2 6
Returns all ids of
table1 that appear with all of values supplied by
Works best for a variety of rows both in tables.
If duplicate rows can be displayed in
table1 make that:
HAVING count(DISTINCT value) = (SELECT count(*) FROM table2)
It appears in my experience that around anything you'd like to learn how you can request the best question. The miracle words listed here are "relational division".
SQL doesn't have explicit divide operator. You will find numerous workarounds using other operator and the best will rely on your needs, including exact division or division with remainder and just how to deal with a clear divisor. Then you will find the typical factors: SQL product and version, performance, personal style and taste, etc.
Here are a handful of articles that ought to assist you with these options:
UPDATE Another possibility:
SELECT t1.id FROM (SELECT t1.id, t1.value FROM table1 t1 JOIN table2 t2 USING (value) GROUP BY t1.id, t1.value ORDER BY t1.id) t1 GROUP BY t1.id HAVING COUNT(*) = (SELECT COUNT(*) FROM table2)
The price of my answer, if you are using EXPLAIN Evaluate is definitely 893-900, despite repeated rows.