Get records from one table, based on related data from another table-CodePudding

I have 2 tables, one with "user visits" and one with "notification info". I want to select user visits for a spesific user in tblUserVisit, based on criterias for this user in tblUserNotify.

If I only select data from tblUserVisit, I get 2 records:

SELECT TOP (1000) [Id]
      ,[SiteId]
      ,[UserName]
      ,[Visited]
      ,[Created]
      ,[Status]
  FROM [tblUserVisit]
  where visited = 'lilje' and siteid='3' and status = '0'

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.

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:

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

I have tried using LEFT JOIN as well, but it did not solved the problem.

This is how tblUserNotify looks like:

Hope oneone can help me modifying the SQL, so it don't returns records twice from tblUserVisits when joining the tblUserNotify table.

CodePudding user response：

I would add an EXISTS clause:

SELECT TOP 1000 [Id]
  ,[SiteId]
  ,[UserName]
  ,[Visited]
  ,[Created]
  ,[Status]
FROM [tblUserVisit] tuv
WHERE visited = 'lilje' AND siteid = '3' AND status = '0' AND
      EXISTS (SELECT 1 FROM [tblUserNotify] tun
              WHERE tun.UserName = tuv.Visited AND [Type] = '7');