Perform SELECT with data from second table

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

General SQL Server Forums

New to SQL Server Programming

Perform SELECT with data from second table

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-07-12 : 12:37:55

Hi.

What I'm trying to do is probably very very easy, but I'm not an experienced SQL programmer. I have a table of blocked users:

My first table is named BLOCKEDUSERS

The column names are "username" and "addedby"

username // addedby
====================
JohnSmith // myself
PeterParker // JohanneX3
JohanneX3 // somebody
Fred2 // TimeRunner

The first column is "username" which is the user that is blocked, the second column is "addedby" which is the user that is blocking them.

Example: Record 1, the user named 'myself' has chosen not to accept messages from 'JohnSmith'

Basically if a person does not want to communicate with another member through my private messaging system, they can add the person's name to the BLOCKEDUSERS table under the username column, and their own username is inserted into 'addedby'.

When you send a private message, this occurs:

strsql = "SELECT * from blockedusers where username = '" & MY_USER_NAMEHERE & "' AND addedby ='" & PERSON_I_AM_CONTACTING & "'"
set objRS = objConn.Execute(strSQL)

if NOT (objRS.BOF and objRS.EOF) then
** Not accepting messages from you error. **
end if

What I would like to do is to also disallow the displaying of comments (shown on another part of my website) from appearing if the person's name is in the "username" field of BLOCKEDUSERS

For example my second table:
Tablename : COMMENTS

column names: cmtname // cmttext // cmtdate // locid

JohnSmith // this is my comment // 1-12-2012 // 23
Someone_3 // this is a great site // 1-10-2012 // 32
JohnSmith // nice pictures! // 3-12-2012 // 32

My current query is:

strSQL = "SELECT * FROM comments WHERE locid = " & val & " ORDER by cmtdate desc"
set objrs = objdb.Execute(strSQL)

and all comments with the same locid are given from newest to oldest regardless of the author (cmtname field).

Basically I would like all recordsets from the COMMENTS table but wish to exclude any comments that have been left by a user listed in the BLOCKEDUSERS table.

Something like this (but this is not a proper SQL command):

strSQL = "SELECT * FROM comments WHERE locid = " & val &
BUT NOT EQUAL TO
(WHERE MY_USERNAME_HERE = addedby in BLOCKEDUSERS TABLE
and WHERE username in BLOCKEDUSERS TABLE = cmtname)
ORDER by cmtdate desc"

So return all records from the comments table but NOT include any where the cmtname (author of the comment) is found in the BLOCKEDUSERS table under USERNAME and where my username is under ADDEDBY. So if I've added their name to the BLOCKEDUSERS, their comment should not be returned in the comments table.

Have I confused you?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-12 : 13:30:36

YOu can do it one of may ways.. LFET OUTER JOIN, NOT IN, NOT EXISTS, etc..Here is one way using a left join:

SELECT 
	* 
FROM 
	comments 
LEFT OUTER JOIN
	BlockedUsers 
	ON comments.locid = BlockedUsers.username 
	AND BlockedUsers.AddedBy = <SomeUserNaneHere>
WHERE 
	BlockedUsers.AddedBy IS NULL
ORDER BY
	cmtdate DESC

oap
Yak Posting Veteran

60 Posts

Posted - 2012-07-12 : 13:59:10

Thank you :) I will try this out and let you know how I make out.

oap
Yak Posting Veteran

60 Posts

Posted - 2012-07-12 : 20:24:39

Okay tried the code and it causes my pages to just hang indefinitely (until timeout). Unfortunately I wasn't using the real life table names for security reasons but will
provoide them below.

COMENTTABLE

COlumns: comments (the comment left by user), name (that user's name), locid (integer) and stamp (date stamp)

The comments are grouped by locid which seprates the comments by location.

BLOCKLIST

usernamefld is the user who is online right now, and has added someone into the blockeduserfld

I require a query of the COMMENTTABLE which will return the results in DESCNDING ORDER by the 'stamp' record. The query must have to exclude any COMMENTTABLE results in which the 'blocklist.blockeduserfld' EQUALS commenttable.namebecause it means the username of the comment is the same username as in the blocklist and blocklist.usernamefld EQUALS <<actively online user>>.

The code given was:

SELECT
*
FROM
comments
LEFT OUTER JOIN
BlockedUsers
ON comments.locid = BlockedUsers.username
AND BlockedUsers.AddedBy = <SomeUserNaneHere>
WHERE
BlockedUsers.AddedBy IS NULL
ORDER BY
cmtdate DESC

However I don't see why I would be referencing comments.locid to equal the blockedusers.username when locid is a numeric value and blockedusers.username should be matched to commenttable.name

I rewrote the sample code provided by the member below to this:

strsql = "SELECT * FROM commenttable LEFT OUTER JOIN Blocklist ON commenttable.name = Blocklist.blockeduserfld "
strsql= strsql & "AND Blocklist.usernamefld = 'mheinc'"
strsql = strsql & " WHERE Blocklist.usernamefld IS NULL ORDER BY stamp DESC"

and it hangs now, no results and eventual timeout.

So to summarize:
1) return all results from COMMENTTABLE and ORDER THEM BY commenttable.stamp descending

2) but SKIP any entries in COMMENTTABLE where we have chosen to block that person - by comparing the name of the comment poster (commenttable.name)

to my second table blockedlist.blockeduserfld and the
and to my second table blockedlist.usernamefld to see if *I* am in that list (any value will do I used mheinc as seen above)

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources