Tough Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tough Query

Author

Topic

sulman
Starting Member

20 Posts

Posted - 2006-11-16 : 04:51:50

Hi,

I have a simple messaging tool that also allows users to block other users.

My table structure is this:

Messages
--------
id
to
from
message
date

I also allow users to block other users so they do not recieve messages from them:

Blocked
-------
personID
blockedPersonID

A sample row would be

Messages
--------
1, 1022, 1443, Hello how are you, 01/01/2006

Blocked
-------
1022, 1443

As you can see user 1022 has blocked user 1433 from sending them messages

I need to return only those messages that were sent to the user but not from one of their blocked people.
Can anyone help me with the SQL statement as I am pulling my hair out!

I hope I have made myself clear.

Thanks

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 04:54:16

[code]SELECT ...
FROM Messages AS M
LEFT OUTER JOIN Blocked AS B
ON B.personID = M.from
WHERE B.personID IS NULL
AND ...
[/code]
Kristen

sulman
Starting Member

20 Posts

Posted - 2006-11-16 : 05:07:10

Wow. Quick reply thanks very much!

I should perhaps have mentioned that this query is also joining on some other tables. And I'm not sure how to "plug in" your suggestion. This is my original query (this returns all, including blocked) :

SELECT Messages.*, FromPeople.Name AS fromname, ToPeople.Name as toname FROM Messages
INNER JOIN People as FromPeople ON Messages.[from]=FromPeople.PeopleId
INNER JOIN People AS ToPeople ON Messages.[to]=ToPeople.PeopleId
WHERE [to]=1286 AND deleted is NULL ORDER BY [posted] DESC

With your suggestion I have tried this:

SELECT Messages.*, FromPeople.Name AS fromname, ToPeople.Name as toname FROM Messages
INNER JOIN People as FromPeople ON Messages.[from]=FromPeople.PeopleId
INNER JOIN People AS ToPeople ON Messages.[to]=ToPeople.PeopleId
LEFT OUTER JOIN Blocked AS B ON B.personID = Messages.from
WHERE B.personID IS NULL AND [to]=1286 AND deleted is NULL ORDER BY [posted] DESC

But It still returns blocked as well.

Any idea how I'm cocking it up?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:10:52

Why don't you give us the whole picture the first time? Doing this you did now, only makes people angry to have put double time into your problem.
Remember, this is a free site where people give of their experience and skills for nothing.

Peter Larsson
Helsingborg, Sweden

sulman
Starting Member

20 Posts

Posted - 2006-11-16 : 05:14:45

Yep you're totally right. I contribute on a number of other free dev forums and so understand your complaints.
However because my SQL is rubbish at best I had no idea that the other bits would be important and was merely trying to simplify the problem in order to make it more understandable.

I will know for the future.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 05:25:38

You need a second binding in the LEFT JOIN row, because of blocked person can be block for other people, not just this one.
Try this

SELECT Messages.*, FromPeople.Name AS fromname, ToPeople.Name as toname FROM Messages
INNER JOIN People as FromPeople ON Messages.[from]=FromPeople.PeopleId
INNER JOIN People AS ToPeople ON Messages.[to]=ToPeople.PeopleId
LEFT OUTER JOIN Blocked AS B ON B.personID = Messages.to AND b.blockedpersonid = messages.from
WHERE B.personID IS NULL AND [to]=1286 AND deleted is NULL ORDER BY [posted] DESC

Peter Larsson
Helsingborg, Sweden

sulman
Starting Member

20 Posts

Posted - 2006-11-16 : 05:42:10

Excellent. That is working a treat.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources