title: SQL many-to-many join and query old data record only
body: <p>I have 4 tables which have a relationship like the following</p>
  <ul>
  <li><code>Brand</code> has many <code>Stores</code></li>
  <li><code>Store</code> has many <code>Brands</code></li>
  <li><code>Brand</code> has many <code>Transaction</code></li>
  <li><code>Transaction</code> belongs to <code>Brand</code>.</li>
  </ul>
  <p><a href="https://i.stack.imgur.com/FRg6y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FRg6y.png" alt="enter image description here" /></a></p>
  <p>Most importantly, I have a concept called <code>mutual brand</code>. Basically is a group of brands that have the same store.</p>
  <p>For example, Nike and Adidas both have store A and the store_id is <code>1</code>.</p>
  <p>In the <code>BrandStore</code> table, we save</p>
  <pre><code>| bs_id | store_id | brand_id |
  |   1   |    1     |     1    | &lt;- (Nike)
  |   1   |    1     |     2    | &lt;- (Adidas)
  </code></pre>
  <p>Transaction</p>
  <pre><code>| t_id  | brand_id | created                |
  |   1   |    1     | 2021-02-25 00:00:00    | &lt;- (Nike)
  |   1   |    1     | 2021-02-25 00:00:00    | &lt;- (Nike)
  </code></pre>
  <p>The question is that, I want to query out all of the brands includes their mutual brand which doesn't have any transactions in the past 30 days.</p>
  <p>For example, in the above example, no brand should be query out. Even <code>Adidas</code> doesn't have any transaction but its mutual brand does.</p>
  <p>Here is the way to query out no recent transaction for a single brand, but I am stuck and don't know how to apply the many-to-many relationship.</p>
  <pre><code>select  b.*
  from brand as b
  where b.brand_id NOT IN (
      select t.brand_id
      from Transaction as t 
      where t.created_at &gt;= DATEADD(day, -30, GETDATE())
  )
  </code></pre>
  <hr />
  <h2>Update</h2>
  <p>Assume we have 4 brands A,B,C,D</p>
  <pre><code>A and B are mutual brands
  A and C are mutual brands
  B and D are mutual brands
  </code></pre>
  <p>Example 1. a recent transaction associate with <code>A</code>.</p>
  <ul>
  <li>The query returns brand <code>D</code>, since B and C would be considered to have a transaction.</li>
  </ul>
  <p>Example 2, a recent transaction associate with <code>B</code>.</p>
  <ul>
  <li>The query returns brand <code>C</code>, since A and D would be considered to have a transaction.</li>
  </ul>
  <p>Example 3, a recent transaction associate with <code>C</code>.</p>
  <ul>
  <li>The query returns brand <code>B</code> and <code>D</code>, since A would be considered to have a transaction.</li>
  </ul>