Help with T-SQL Joins

So I have a table that links IP's to votes/flags of messages called tblIPMessageLink

It contains the IP that voted or flagged, and a bit column for 'voted' and 'flagged'.

The getMessages proc is below

SELECT
  m.message,
  m.votes,
  m.messageId,
  ml.voted,
  ml.flagged,
  NULL as row开发者_运维百科s
FROM
  tblMessages m
left join tblIPMessageLink ml on m.messageid = ml.messageid
WHERE
  m.timestamp >= DATEADD(day, DATEDIFF(day, 0, @date), 0)
  and
  m.timestamp < DATEADD(day, DATEDIFF(day, 0, @date), 1)
  and
  datediff(hour,m.timestamp, getdate()) <= 2
ORDER BY datediff(hour,m.timestamp, getdate()) DESC

Even if I exec this proc passing in an IP that does NOT exist in the link table, it still comes up as having voted for the message. It shouldn't, because I voted from a different IP.

What am I doing wrong?

I believe that the left join means that the query is going to list all rows in the table on the "left" (the message table) and display the corresponding row or null from the other table (the link table). if you're taking the existence of a result row as the existence of a vote, that's your problem. You're going to get back values for all the m rows and NULL for ml.voted and ml.flagged.

The solution is to switch the tables' position in the sproc.

Based on your comment

It's returning a 'true' bit in those cases, when it should be returning NULL.

and on the query you posted I think that the problem is in the data. There is no way a query for a particular field can return anything but this field or NULL. If you see true, then true is what is stored in this field for corresponding MessageId Could you please:

• Choose a single MessageId that exhibits the problem
• Run you query filtering on this MessageId and post here the resulting recordset
• Dump all the rows from tblMessages with chosen MessageId and post here
• Dump all the rows from tblIPMessageLink with chosen MessageId and post here

It would also help if you provide:

• Definition of tblMessages (along with keys, constraints, etc)
• Definition of tblIPMessageLink (along with keys, constraints, etc)

Figured it out, my join line should look like this:

left join tblIPMessageLink ml on m.messageid = ml.messageid and ml.ip = @ip