Query a table where combinations of fields do not exit?

Question

I apologize for the poorly worded title :(

I have three tables.

Table MARA: Articles and their descriptions.
Table MARC: Article to site assortments.
Table T001W: Sites and their locations.

I'm trying to find the missing records in MARC that demonstrate an article is NOT assorted to a site. See the attached table for a rudimentary example.

Here, there are 2 articles (A & B) and 4 sites (1, 2, 3, and 4). I'm looking for the query that can search MARC based upon the articles in MARA and the sites in MARC and tell me what doesn't exist. The right answers in this example would be A-4, B-1, and B-4.

Alternatively, I can provide the MARA and T001W values in a list in the query to avoid any joins. MARA will have ~1,500 values and T001W will have 2,000.

<style type="text/css">
  .tg {
    border-collapse: collapse;
    border-spacing: 0;
  }
  
  .tg td {
    font-family: Arial, sans-serif;
    font-size: 14px;
    padding: 10px 5px;
    border-style: solid;
    border-width: 1px;
    overflow: hidden;
    word-break: normal;
    border-color: black;
  }
  
  .tg th {
    font-family: Arial, sans-serif;
    font-size: 14px;
    font-weight: normal;
    padding: 10px 5px;
    border-style: solid;
    border-width: 1px;
    overflow: hidden;
    word-break: normal;
    border-color: black;
  }
  
  .tg .tg-amwm {
    font-weight: bold;
    text-align: center;
    vertical-align: top
  }
  
  .tg .tg-8m24 {
    background-color: #000000;
    text-align: left;
    vertical-align: top
  }
  
  .tg .tg-8zwo {
    font-style: italic;
    text-align: left;
    vertical-align: top
  }
  
  .tg .tg-0lax {
    text-align: left;
    vertical-align: top
  }
</style>
<table class="tg">
  <tr>
    <th class="tg-amwm" colspan="2">MARA</th>
    <th class="tg-8m24"></th>
    <th class="tg-amwm" colspan="2">MARC</th>
    <th class="tg-8m24"></th>
    <th class="tg-amwm" colspan="2">T001W</th>
  </tr>
  <tr>
    <td class="tg-8zwo">Article</td>
    <td class="tg-8zwo">Desc</td>
    <td class="tg-8m24"></td>
    <td class="tg-8zwo">Article</td>
    <td class="tg-8zwo">Site</td>
    <td class="tg-8m24"></td>
    <td class="tg-8zwo">Site</td>
    <td class="tg-8zwo">Continent</td>
  </tr>
  <tr>
    <td class="tg-0lax">A</td>
    <td class="tg-0lax">Spoon</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">A</td>
    <td class="tg-0lax">1</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">1</td>
    <td class="tg-0lax">NA</td>
  </tr>
  <tr>
    <td class="tg-0lax">B</td>
    <td class="tg-0lax">Fork</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">A</td>
    <td class="tg-0lax">2</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">2</td>
    <td class="tg-0lax">SA</td>
  </tr>
  <tr>
    <td class="tg-0lax"></td>
    <td class="tg-0lax"></td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">A</td>
    <td class="tg-0lax">3</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">3</td>
    <td class="tg-0lax">EU</td>
  </tr>
  <tr>
    <td class="tg-0lax"></td>
    <td class="tg-0lax"></td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">B</td>
    <td class="tg-0lax">2</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">4</td>
    <td class="tg-0lax">AS</td>
  </tr>
  <tr>
    <td class="tg-0lax"></td>
    <td class="tg-0lax"></td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax">B</td>
    <td class="tg-0lax">3</td>
    <td class="tg-8m24"></td>
    <td class="tg-0lax"></td>
    <td class="tg-0lax"></td>
  </tr>
</table>

Edit: Adding text tables and sample data.

Table MARA
Article | Description
A | Spoon
B | Fork

Table MARC
Article | Site
A | 1
A | 2
A | 3
B | 2
B | 3

Table T001W
Site | Desc.
1 | NA
2 | SA
3 | EU
4 | AS

Sample data and desired results *as text tables* would really help! — Gordon Linoff, Feb 12 '20 at 13:27
Sorry, Gordon, I thought the HTML table would be more easily understood. I've edited my post to try to recreate it in text. — user2547757, Feb 12 '20 at 14:08

score 0 · Answer 1 · answered Feb 12 '20 at 13:29

0

If I understand correctly, you can use a cross join to generate all combinations of sites and articles and then remove the ones that exist:

select s.site, a.article
from sites s cross join
     articles a left join
     marc m
     on m.article = a.article and
        m.site = s.site
where m.article is null;

I renamed the tables so the query logic is easier to follow.

answered Feb 12 '20 at 13:29

Gordon Linoff

1,242,037
58
646
786

Thanks for the assistance. I manipulated the values back to my column and table names for execution but hit memory allocation errors. MARC is a table with 770 mil + values, so how can this be adjusted to check a given list instead of joining? – user2547757 Feb 12 '20 at 14:23
1

@user2547757 . . . Use derived tables for either `sites` or `articles` to limit the sites/articles being considered. You can also add a `where` clause to filter the tables -- `where s.site in (. . .) and a.article in (. . .)`. – Gordon Linoff Feb 12 '20 at 15:31

Query a table where combinations of fields do not exit?

1 Answers1