title: Get records from one table, based on related data from another table
body: <p>I have 2 tables, one with &quot;user visits&quot; and one with &quot;notification info&quot;. I want to select user visits for a spesific user in tblUserVisit, based on criterias for this user in tblUserNotify.</p>
  <p>If I only select data from tblUserVisit, I get 2 records:</p>
  <pre><code>SELECT TOP (1000) [Id]
        ,[SiteId]
        ,[UserName]
        ,[Visited]
        ,[Created]
        ,[Status]
    FROM [tblUserVisit]
    where visited = 'lilje' and siteid='3' and status = '0'
  </code></pre>
  <p><a href="https://i.stack.imgur.com/7vgTc.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7vgTc.jpg" alt="Result from query" /></a></p>
  <p>So far, so good. Now I want to check if users in the result from tblUserVisit has Notification Type = 7 in tblUserNotify. Only users with Notification Type = 7 shall be returned.</p>
  <p>When doing that, I get multiple records. The records with tblUserNotify.Value = nothing is correct (first and third record). I also get 2 records with tblUserNotify.Value = 0. Here is my code:</p>
  <pre><code>SELECT        tblUserVisit.Id, tblUserVisit.SiteId, tblUserVisit.UserName, tblUserVisit.Visited, tblUserVisit.Created, tblUserVisit.Status, tblUserNotify.Type, tblUserNotify.Value
  FROM            tblUserVisit INNER JOIN
                           tblUserNotify ON tblUserVisit.Visited = tblUserNotify.UserName
  WHERE        (tblUserVisit.Status = '0') AND (tblUserVisit.SiteId = '3') AND (tblUserNotify.Type = '7') AND (tblUserVisit.Visited = 'lilje')
  ORDER BY tblUserVisit.Id
  </code></pre>
  <p><a href="https://i.stack.imgur.com/akmo6.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/akmo6.jpg" alt="Result" /></a></p>
  <p>I have tried using LEFT JOIN as well, but it did not solved the problem.</p>
  <p>This is how tblUserNotify looks like:</p>
  <p><a href="https://i.stack.imgur.com/yvFRL.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yvFRL.jpg" alt="tblUserNotify" /></a></p>
  <p>Hope oneone can help me modifying the SQL, so it don't returns records twice from tblUserVisits when joining the tblUserNotify table.</p>