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
 [solved] Three table join (help)

Author  Topic 

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2014-07-06 : 20:56:16
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

yado
Starting Member

8 Posts

Posted - 2014-07-07 : 03:36:33
unspammed
Go to Top of Page

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?
Go to Top of Page

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;
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -