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.
Author |
Topic |
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2014-07-06 : 20:56:16
|
HelloI am trying to tweak some code which is used to display the newest comments left on photos created by my members.The existing code is this:SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESCso the latest comment left was for photo #210879 from user "Cla" (redacted user names). The 2nd newest comment would be for photo #211072 from a member named "mo". pdate is a date field However for the script I have coded I don't want all of the photo comments to show up. This is because I use access levels based on the type of location (higher levels mean more restricted galleries). I check the access levels as I go through the recordsets.I use this method to get the top 15 comments:SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESCNow I have to use two other tables to determine the access level. Since PHOTOCOMMENTS is just a list of photo #'s and the people who left comments for those photos, I need to:a) determine what location the photo is from andb) determine the access level of that location I use:select creator,access from locations where id=(select dir from photos where id="&pnumber&")"This is a two step process as you can see. The first part is:select dir from photos where id=(pnumber)ID is the same value as pnumber seen in PHOTOCOMMENTS. That is to say PHOTOS.ID = PHOTOCOMMENTS.PNUMBERIf I haven't confused you yet, the executed code for the first example would be:select dir from photos where id=210879which would get me a value for DIR. DIR is the location number which would be:select creator,access from locations where id=(dir value)Just to simplify it a bit....There are three tables (shown below)PHOTOCOMMENTSPHOTOSLOCATIONSI need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)but then alsoselect creator,access from locations (The last table shown)where id=(select dir from photos where id="&pnumber&")"So the first table PHOTOCOMMENTS has to also join PHOTOS table where PHOTOS.DIR = PHOTOCOMMENTS.PNUMBER in order to get the value of "DIR" and then DIR is joined to the LOCATIONS tables where PHOTOS.DIR = LOCATIONS.IDI'm sorry if I'm confusing people I can't explain it any better.Here is the actual code, which I am trying to make into a single SQL commandstrSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC"set ors = oconn.Execute(strSQL)tl = 0do until ors.eof or tl > 15' until we have 15 results because not every recordset will be of the proper security levelnam = ors("puser")com = left(ors("pcomment"),60)pnumber=ors("pnumber")strsql2= "select creator,fullmember from locaitons where id=(select dir from photos where id="&pnumber&")"set ors2 = oconn.Execute(strSQL2)fmac = ors2("fullmember")if userlevel >= fmac thenresponse.write "you have access to pic #" & pnumbertl = tl+1end ifors.movenext loopBonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2 |
|
yado
Starting Member
8 Posts |
Posted - 2014-07-07 : 03:36:33
|
unspammed |
|
|
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2014-07-07 : 19:09:30
|
The one reply I receive is 'unspammed'. Doesn't anyone have an answer? |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-07-08 : 13:48:02
|
Try this:declare @userlevel int;select top(15) p.id ,left(pc.pcomment,60) as pcomment ,pc.puser ,l.creator ,l.fullmember from photos as p inner join photocomments as pc on pc.pnumber=p.id inner join locations as l on l.id=p.dir where l.fullmember<=@userlevel and l.userlevel>=2 order by pc.pdate desc; |
|
|
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2014-07-09 : 21:15:52
|
You are a genius (in my mind), it worked great. I had to remove the l.userlevel>=2 but it does what I want :)Thank you! |
|
|
|
|
|
|
|