{"id":15031,"date":"2022-10-10T03:51:11","date_gmt":"2022-10-09T22:21:11","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/"},"modified":"2022-10-10T03:51:11","modified_gmt":"2022-10-09T22:21:11","slug":"solved-sql-select-looking-for-a-name-like-a-substring-in-a-join","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/","title":{"rendered":"[Solved] SQL SELECT looking for a name like a substring in a join"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-39810006\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"39810006\" data-parentid=\"39809599\" data-score=\"1\" 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>I would read the &#8220;not certified on any&#8221; to be a check for the existence of a row.<\/p>\n<p>If a matching row exists, then don&#8217;t return the employee. Only return the emplouee if a matching row doesn&#8217;t exist.<\/p>\n<p>How would you find a matching row, to find out if an employee is &#8220;certified on any&#8221;?<\/p>\n<p>There are several approaches. The two best approaches to use 1) anti-join and 2) NOT EXISTS (correlated subquery).<\/p>\n<hr>\n<p>example <strong><code>NOT EXISTS (correlated subquery)<\/code><\/strong><\/p>\n<p>Of the two approaches this one is easier to see how it works.<\/p>\n<pre><code>  FROM e\n WHERE NOT EXISTS ( SELECT 1\n                      FROM certified c\n                      JOIN aircraft a \n                        ON a.id = c.a_id\n                     WHERE a.aname LIKE '%b%'   \n                       AND c.e_id = e.id\n                  ) \n<\/code><\/pre>\n<p>Note the reference to the outer table (e.id) in the predicate of the subquery. The subquery is &#8220;correlated&#8221; with the outer query. <\/p>\n<p>Think of if this way: for every row returned by the outer query, the subquery is executed, passing in the value of <code>e.id<\/code>. (The optimizer doesn&#8217;t <em>have<\/em> to perform the operation this way; that&#8217;s just an easy way of thinking about what we&#8217;re asking for.)<\/p>\n<p>If the subquery returns 1 or more rows, the condition EXISTS is satisfied, and returns TRUE. If the subquery returns zero rows, EXISTS evaluates to FALSE.<\/p>\n<hr>\n<p>example of <strong>anti-join pattern<\/strong> <\/p>\n<p>This approach can take a bit to get your brain wrapped around. Once you do &#8220;get it&#8221;, it&#8217;s an invaluable tool to keep handy in the SQL toolbelt. <\/p>\n<p>If we use an OUTER JOIN, and pull back all rows from <code>e<\/code> along with any matching rows, then we can &#8220;exclude&#8221; the rows that found a match.<\/p>\n<pre><code>  FROM e\n  LEFT\n  JOIN ( SELECT c.e_id\n           FROM certified c\n           JOIN aircraft a\n             ON a.id = c.a_id\n          WHERE a.aname LIKE '%b%'\n         GROUP BY c.e_id\n       ) b\n    ON b.e_id = e.id\n WHERE b.e_id IS NULL\n<\/code><\/pre>\n<p>The inline view query is materialized into a derived table named <code>b<\/code>. That query is intended to return the id of every employee that is certified to fly any aircraft meeting the specified criteria. Then the rows in the derived table are outer joined to <code>e<\/code>.<\/p>\n<p>The &#8220;trick&#8221; is the outer join (to include both rows with matches, and rows without matches, and the condition in the WHERE clause that excludes rows that had matches.<\/p>\n<hr>\n<p>I expect someone else will provide an example of how to use a <code>NOT IN (subquery)<\/code>. With that approach, beware of what happens if the subquery returns any NULL values. (HINT: you will want to ensure that the subquery will never ever return a NULL.)<\/p>\n<p>This demonstrates only two of several possible approaches to satisfying the &#8220;is not certified on any&#8221; criteria.<\/p>\n<p>Obviously, additional joins\/subqueries will need to be added to evaluate the other criteria in the query. <\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\"><\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved SQL SELECT looking for a name like a substring in a join <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] I would read the &#8220;not certified on any&#8221; to be a check for the existence of a row. If a matching row exists, then don&#8217;t return the employee. Only return the emplouee if a matching row doesn&#8217;t exist. How would you find a matching row, to find out if an employee is &#8220;certified on &#8230; <a title=\"[Solved] SQL SELECT looking for a name like a substring in a join\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\" aria-label=\"More on [Solved] SQL SELECT looking for a name like a substring in a join\">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":[340,341,500,1009],"class_list":["post-15031","post","type-post","status-publish","format-standard","hentry","category-solved","tag-mysql","tag-sql","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 looking for a name like a substring in a join - 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-looking-for-a-name-like-a-substring-in-a-join\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL SELECT looking for a name like a substring in a join - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] I would read the &#8220;not certified on any&#8221; to be a check for the existence of a row. If a matching row exists, then don&#8217;t return the employee. Only return the emplouee if a matching row doesn&#8217;t exist. How would you find a matching row, to find out if an employee is &#8220;certified on ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-09T22:21:11+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=\"2 minutes\" \/>\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-looking-for-a-name-like-a-substring-in-a-join\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL SELECT looking for a name like a substring in a join\",\"datePublished\":\"2022-10-09T22:21:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\"},\"wordCount\":419,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"mysql\",\"sql\",\"sql-server\",\"tsql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\",\"name\":\"[Solved] SQL SELECT looking for a name like a substring in a join - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-09T22:21:11+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL SELECT looking for a name like a substring in a join\"}]},{\"@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=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"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 looking for a name like a substring in a join - 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-looking-for-a-name-like-a-substring-in-a-join\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL SELECT looking for a name like a substring in a join - JassWeb","og_description":"[ad_1] I would read the &#8220;not certified on any&#8221; to be a check for the existence of a row. If a matching row exists, then don&#8217;t return the employee. Only return the emplouee if a matching row doesn&#8217;t exist. How would you find a matching row, to find out if an employee is &#8220;certified on ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/","og_site_name":"JassWeb","article_published_time":"2022-10-09T22:21:11+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL SELECT looking for a name like a substring in a join","datePublished":"2022-10-09T22:21:11+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/"},"wordCount":419,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["mysql","sql","sql-server","tsql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/","name":"[Solved] SQL SELECT looking for a name like a substring in a join - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-09T22:21:11+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-select-looking-for-a-name-like-a-substring-in-a-join\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL SELECT looking for a name like a substring in a join"}]},{"@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=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","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\/15031","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=15031"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/15031\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=15031"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=15031"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=15031"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}