Sql syntax to always get one

SELECT  dbo.Calls.Description, dbo.TicketRead.IsRead, dbo.TicketRead.UserID
FROM    dbo.Calls 
LEFT OUTER JOIN dbo.TicketRead ON dbo.Calls.CallID = dbo.TicketRead.TicketID 
WHERE dbo.TicketRead.UserID = 1 or is null

I want to get 开发者_如何学编程a list of all calls, but also a value indicating if the user have read the call. so I made when a user open's the ticket a new record is added in a special table.

now if no user have read it then it's ok, but if just one user have read this ticket, then the other users don't have this call in the list..

I'm using msSql and .net entity framework.

---

Are you sure you want to limit the TicketRead table to just UserID # 1?

SELECT  c.Description, r.IsRead, r.UserID
FROM    dbo.Calls c
LEFT JOIN dbo.TicketRead r 
   ON c.CallID = r.TicketID 
   AND r.UserID = 1

This is done joining on multiple conditions... You'll get all of the Calls, but only the IsRead flag from TicketRead when UserID #1 has read it.

I'm not convinced this is actually what you're looking for, and I will be available to update this later if not.

Also please consider using table aliases in the future, I think you'll like it.

---

you are wanting to do the following:

SELECT  dbo.Calls.Description, dbo.TicketRead.IsRead, dbo.TicketRead.UserID
FROM    dbo.Calls 
LEFT OUTER JOIN dbo.TicketRead ON dbo.Calls.CallID = dbo.TicketRead.TicketID 
WHERE 
dbo.TicketRead.UserID = 1 
or dbo.TicketRead.CallID IS NULL

basically, we are saying here, if there a userid, it has to be 1. otherwise, the records primary key is null (because of the join). Fosco's answer is also right, and much smaller :)