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
 [solved] Three table join (help)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Starting Member

Canada
43 Posts

Posted - 07/06/2014 :  20:56:16  Show Profile  Reply with Quote
Hello

I 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 DESC



so 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 DESC

Now 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 and
b) 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.PNUMBER

If I haven't confused you yet, the executed code for the first example would be:
select dir from photos where id=210879
which 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)
PHOTOCOMMENTS
PHOTOS
LOCATIONS

I need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)

but then also
select 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.ID




I'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 command

strSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC"
set ors = oconn.Execute(strSQL)
tl = 0

do until ors.eof or tl > 15
' until we have 15 results because not every recordset will be of the proper security level

nam = 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 then
response.write "you have access to pic #" & pnumber
tl = tl+1
end if
ors.movenext
loop

Bonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2

Edited by - sqlconfused on 07/09/2014 21:14:57

yado
Starting Member

Iraq
8 Posts

Posted - 07/07/2014 :  03:36:33  Show Profile  Reply with Quote
unspammed
Go to Top of Page

sqlconfused
Starting Member

Canada
43 Posts

Posted - 07/07/2014 :  19:09:30  Show Profile  Reply with Quote
The one reply I receive is 'unspammed'. Doesn't anyone have an answer?

Edited by - sqlconfused on 07/07/2014 19:10:25
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

322 Posts

Posted - 07/08/2014 :  13:48:02  Show Profile  Reply with Quote
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;
Go to Top of Page

sqlconfused
Starting Member

Canada
43 Posts

Posted - 07/09/2014 :  21:15:52  Show Profile  Reply with Quote
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!
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.16 seconds. Powered By: Snitz Forums 2000