Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Yak Posting Veteran

Canada
50 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
Yak Posting Veteran

Canada
50 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
Aged Yak Warrior

545 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
Yak Posting Veteran

Canada
50 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  
 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.05 seconds. Powered By: Snitz Forums 2000