SQL Retrieval on joined tables not produce desired results

peter

4y987SQL

I have the following sqldatasource query statement that returns wrong results. The output depends on rows in the Business-Rating table.So if there are two rows, it returns all rows that match. while i want it to return all rows that match the MarketPlace_SAddress table instead. So for each row in [Inbox_Messages] that match MarketPlace_SAddress table i want it to return all the data present plus the data in [Business-Rating] table.

So i should in theory retrieve 3 rows that include address details. Yet i only get address details on 2 rows.

The sql was generated from sqldatasource wizard!

SelectCommand="SELECT * FROM Inbox_Messages LEFT OUTER JOIN MarketPlace_SAddress left outer JOIN [Business-Rating] ON MarketPlace_SAddress.UserName = [Business-Rating].Business_UserName ON Inbox_Messages.InboxId = [Business-Rating].MessageId WHERE (Inbox_Messages.RecieverID = @Recieverid or senderid=@recieverid or username=@recieverid) AND (Inbox_Messages.MessageType = 'Services' ) OR (Inbox_Messages.MessageType = 'Services' ) AND (Inbox_Messages.SenderID = @Recieverid) "

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
Test this
SELECT *
FROM [Business-Rating] BR
LEFT JOIN (
    SELECT MS.*
        ,IM.*
    FROM [Business-Rating] BR WITH (NOLOCK)
    JOIN MarketPlace_SAddress MS WITH (NOLOCK) ON BR.UserName = MS.Business_UserName
    WITH (NOLOCK)
    JOIN Inbox_Messages IM WITH (NOLOCK) ON IM.InboxId = BR.MessageId
    ) t
WHERE (
        t.RecieverID = @Recieverid
        OR senderid = @recieverid
        OR username = @recieverid
        )
    AND (t.MessageType = 'Services')
    OR (t.MessageType = 'Services')
    AND (t.SenderID = @Recieverid)
Jignesh Kumar4y ago
Hi Peter,

With (NOLOCK) does following ,
- The NOLOCK allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes.
- This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.
peter4y ago
i managed to get original query to work by simply swap the join tables around. I think your idea is too complex for implementation. I was wondering what the WIT (NOLOCK) does?