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
 Combining 3 select statements

Author  Topic 

oap
Yak Posting Veteran

60 Posts

Posted - 2013-01-29 : 16:27:02
Hi there!

I have three SQL statements that occur consecutively. What happens is a person will leave a comment for a photo and if the author of the photo has enabled email notifications, a message is sent out to them alerting them of a new comment.

There is an easier way to do this, I just don't know how.

recid = 333 (example: photo is numbered 333)
strSQL = "SELECT * FROM photolist WHERE id = " & recid

set objRS = objConn.Execute(strSQL)
vdir = objrs("directory")

^ vdir is a value indicating what location number the photo is for. every location is numbered

strSQL = "SELECT * FROM sitelist WHERE ID = " & vdir
set objRS = objConn.Execute(strSQL)
whomade = objrs("createdby")

^ fetch the name of the person that created the entry

strSQL = "SELECT email,notifycmnt FROM userlist WHERE username = '" & whomade & "'"
set objRS = objConn.Execute(strSQL)
^ fetch email address and notify flag from the user that made entry

and then if notifycmnt = "yes" then send a message

So lets say that photo 333 receives a comment, it will pull the directory first, which let's say would be "My CN Tower photos".

Then it goes back to 'sitelist' to retrieve from the table where "My CN Tower photos" is, the "createdby" field.

Let's say that 'createdby' for 'My CN Tower photos" is "George1"

Then it gets email and notifycmnt from the user database for "George1" to grab his email address and check if the notify-new-comment field says "yes"

Can you help?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-29 : 17:03:54
You can join all three tables together - like shown below (I am showing the T-SQL; you would concatenate with the recid to produce the query string shown below)
SELECT
u. email,
u.notifycmnt
FROM
photolist p
INNER JOIN sitelist s ON s.ID = p.directory
INNER JOIN userlist u ON u.username = s.createdby
WHERE
p.id = 333;
Go to Top of Page

oap
Yak Posting Veteran

60 Posts

Posted - 2013-01-29 : 17:23:00
Thanks for the response. I tested it under MS SQL and it is hit and miss. It worked a few times but now I receive no rows returned even though the fields are valid.

Ironically where I put in a value for p.id that doesn't exist (a deleted location) it DOES return an email address, I just don't know whose.





Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-29 : 18:09:18
That sounds like the join conditions may not be correct. Does directory column on photolist table correspond to the ID column on sitelist table? And does the createdby column on sitelist correspond to username table on userlist table?

Another thing to consider is whether every photolist has a non-null directory value and whether every sitelist has a non-null createdby value. If that is not the case, you would get no row for that combination.

Take one example (one row) where it misses and examine the values in each of the tables. If you can post some sample data that would help.
Go to Top of Page

oap
Yak Posting Veteran

60 Posts

Posted - 2013-01-29 : 19:03:14
Oh man. I apologize, I was using the location # instead of the photo #.

Your code worked perfectly! I feel like I should be paying money each time someone posts a solution, it saves so much time.

Thank you!
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-01-29 : 21:18:17
I'll pay *you* money if you stop using concatenated values and use parameterised queries instead!
My 2 cents (which is what I'll pay you :) ). You can pay it back when you've learned about SQL injection that stems from not using parameters.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 07:39:44
Even though LoztInSpace is saying what he is saying in a light-hearted manner, the threat of SQL injection is serious and real. For example, even though you are expecting someone to provide a number such as 333 for the recid, a malicious user can send something like shown below - where the "create havoc" can be pretty much anything.
333; CREATE HAVOC ON OAPS_DATABASE;
And that is only the beginning. Hence the advice against concatenated values. If you use a parameterized query, ideally a stored procedure, they would not be able to do such strange stuff to your database.
Go to Top of Page

oap
Yak Posting Veteran

60 Posts

Posted - 2013-01-30 : 09:12:29
I understand.

SQL is not my best area of expertise but I do use this code:

val = left(request.querystring("value"),5)
val = replace(val,"'","")

So if someone is going to perform SQL injection they'd have to do it in 5 characters or less and without the use of an apostrophe.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 09:31:30
Great! I am no expert on SQL injection, but that seems safe enough.
Go to Top of Page
   

- Advertisement -