SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combining 3 select statements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oap
Yak Posting Veteran

USA
60 Posts

Posted - 01/29/2013 :  16:27:02  Show Profile  Reply with Quote
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?

Edited by - oap on 01/29/2013 16:27:52

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 01/29/2013 :  17:03:54  Show Profile  Reply with Quote
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

USA
60 Posts

Posted - 01/29/2013 :  17:23:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 01/29/2013 :  18:09:18  Show Profile  Reply with Quote
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

USA
60 Posts

Posted - 01/29/2013 :  19:03:14  Show Profile  Reply with Quote
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 - 01/29/2013 :  21:18:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 01/30/2013 :  07:39:44  Show Profile  Reply with Quote
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

USA
60 Posts

Posted - 01/30/2013 :  09:12:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 01/30/2013 :  09:31:30  Show Profile  Reply with Quote
Great! I am no expert on SQL injection, but that seems safe enough.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000