{"id":19347,"date":"2022-11-06T08:51:37","date_gmt":"2022-11-06T03:21:37","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/"},"modified":"2022-11-06T08:51:37","modified_gmt":"2022-11-06T03:21:37","slug":"solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/","title":{"rendered":"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-70736303\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"70736303\" data-parentid=\"70734268\" data-score=\"2\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p><strong>This is a classic <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/divided-we-stand-the-sql-of-relational-division\/\"><em>Relational Division With Remainder<\/em><\/a> question.<\/strong><\/p>\n<p>You just need to frame it right:<\/p>\n<ul>\n<li>You want all <code>Tasks<\/code>&#8230;<\/li>\n<li>&#8230; whose <code>TaskTags<\/code> divide the set of all <code>UserTags<\/code> for a given <code>User<\/code><\/li>\n<li>There can be a remainder of <code>UserTags<\/code> but not a remainder of <code>TaskTags<\/code> so the former is the dividend, the latter is the divisor.<\/li>\n<\/ul>\n<p>A typical solution (there are many) is to left join the dividend to the divisor, group it up, then ensure that the number of matched dividends is the same as the number of divisors. In other words, all divisors have a match.<\/p>\n<p>Since you only seem to want the <code>Tasks<\/code> but not their <code>TaskTags<\/code>, you can do all this in an <code>EXISTS<\/code> subquery:<\/p>\n<pre class=\"lang-sql prettyprint-override\"><code>DECLARE @userId int = 1;\n\nSELECT *\nFROM Tasks t\nWHERE EXISTS (SELECT 1\n    FROM TaskTags tt\n    LEFT JOIN UserTags ut ON ut.TagId = tt.TagId\n        AND ut.UserId = @userId\n    WHERE tt.TaskId = t.TaskId\n    HAVING COUNT(*) = COUNT(ut.UserId)\n);\n<\/code><\/pre>\n<p><a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/dbfiddle.uk\/?rdbms=sqlserver_2019&amp;fiddle=30b5f7c509592984b9fc853d09739b38\">db&lt;&gt;fiddle<\/a><\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved SQL Select from table where joined values from a second table are a subset of values from a third table <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] This is a classic Relational Division With Remainder question. You just need to frame it right: You want all Tasks&#8230; &#8230; whose TaskTags divide the set of all UserTags for a given User There can be a remainder of UserTags but not a remainder of TaskTags so the former is the dividend, the latter &#8230; <a title=\"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\" aria-label=\"More on [Solved] SQL Select from table where joined values from a second table are a subset of values from a third table\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[4613,500,1009],"class_list":["post-19347","post","type-post","status-publish","format-standard","hentry","category-solved","tag-relational-division","tag-sql-server","tag-tsql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] This is a classic Relational Division With Remainder question. You just need to frame it right: You want all Tasks&#8230; &#8230; whose TaskTags divide the set of all UserTags for a given User There can be a remainder of UserTags but not a remainder of TaskTags so the former is the dividend, the latter ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-06T03:21:37+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table\",\"datePublished\":\"2022-11-06T03:21:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\"},\"wordCount\":158,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"relational-division\",\"sql-server\",\"tsql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\",\"name\":\"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-11-06T03:21:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table - JassWeb","og_description":"[ad_1] This is a classic Relational Division With Remainder question. You just need to frame it right: You want all Tasks&#8230; &#8230; whose TaskTags divide the set of all UserTags for a given User There can be a remainder of UserTags but not a remainder of TaskTags so the former is the dividend, the latter ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/","og_site_name":"JassWeb","article_published_time":"2022-11-06T03:21:37+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table","datePublished":"2022-11-06T03:21:37+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/"},"wordCount":158,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["relational-division","sql-server","tsql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/","name":"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-11-06T03:21:37+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-from-table-where-joined-values-from-a-second-table-are-a-subset-of-values-from-a-third-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/19347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=19347"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/19347\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=19347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=19347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=19347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}