Reading a table and just one external recordset ??
-
Hi. This is probably a simple issue. I've spent an hour on it trying to get the code to work but without success.
I code in ASP Classic and call this string:
SELECT ID,category,creator,curstatus,town,new,pics,galcount,link,crdate,access,new,lastmod FROM sites where closest ='toronto' and province = 'Ontario' and (fm2<>'Y' or fm2 is null)
This pulls a list of places in the city of Toronto according to category (tourism, etc.), pics is total photos for entry, current status, galcount is # of photo galleries for the entry, crdate is creation date, lastmod is last modified date, link is the URL to the entry.
The fm2 ensures the entry is NOT fm2, which are reserved places for higher access members.
I then display each location for that city... there are roughly 5-200 entries per city. It's not a CPU intensive process. So I'd display each of the above entries for a user.However there is a catch. The field titled "access" is a number from 1-5 which indicates if the user has access to the location. So for example, in the entry above for id #10105, "Chassis Service Ltd" access might be a 3. If you're user level is 3 or higher, you can view the entry. Easy.
Now ordinarily one might just add "and access >= x" where x is your own userlevel, to return only entries for which you have permission to.
This is where it gets messy.... As I'm processing the recordsets, if you have access they are displayed.. end of story.
If however you DON'T have access (say your userlevel is a 1 for Chassis Service Ltd. which is a level 3 access entry) what I do is call a second routine to see if you have a set of photos for that location. This means that even though you don't have access to it, because you've uploaded photos of it in the past, you're entitled to see the entry.
This is the actual code:
allow = 0
if (userlevel >= access) then allow = 1 '
' do they have a gallery
strSQL3 = "SELECT 1 from gallerys where gdir='10105' AND gcreator = 'pacman'"
set objRS2 = objConn.Execute(strSQL3)
if NOT (objrs2.BOF and objrs2.EOF) then allow = 1
objrs2.close
set objrs2 = nothing
end ifSo for every one of 200 potential recordsets, I'm ALSO calling a second query to see if there exists an entry in the 'gallerys' table where gallerys.gdir = sites.id and gcreator = your user name. So if user pacman is a userlevel 1, then it checks gallerys to see if you have a record for that gdir (10105) and sets allow to 1 if you're allowed.
So if you're an access level 5 person, the CPU load is minimal but for an access level 1 user, this essentially means that for 200 records I'm also calling 200 secondary queries to the 'gallerys' table.
What I essentially need is something like:
SELECT ID,category,creator,curstatus,town,new,pics,galcount,link,crdate,access,new,lastmod FROM sites where closest ='toronto' and province = 'Ontario' and (fm2<>'Y' or fm2 is null)
INNER JOIN GALLERYS B ON A.ID = B.GDIR
I just need to know if when sites.id = gallerys.gdir and gallerys.gcreator = 'pacman' to show a recordset. But it's all or nothing in my testing.. meaning I either get a full recordset from SITES or I get no results at all if you're not in the Gallerys table. I still need the above image results but also the additional record.
Thank you
-
something like this should do the trick.
the "allow" column returned by the SQL you only use for validation, and do not show on your page.
If for some reason for a ID there may be more than 1 record on GALLERYS change the left outer join to a outer apply .. select top 1
select a.ID
, a.category
, a.creator
, a.curaatus
, a.town
, a.new
, a.pics
, a.galcount
, a.link
, a.crdate
, a.access
, a.new
, a.laamod
, case
when b.GDIR is not null -- it has a gallery record
or 'userlevel' >= a.access -- userlevel greater than sites access
then 1
else 0
end as allow
from sites a
left outer join gallerys b
on b.GDIR = a.ID
and b.gcreator = 'pacman'
where a.closea = 'toronto'
and a.province = 'Ontario'
and (a.fm2<>'Y' or a.fm2 is null)
assumption that all constant values on the sql above are in reallity variables that you pass to the SQL -
Oh my god.
Awesome! It worked right out of the box. Thank you so much.
-
One issue though, yes... for more than one entry in Gallery I'm seeing duplicate recordsets. I don't know where to insert the outer apply though.
-
you should then do yourself a favor and search for outer apply examples and study them.
they are incredibly powerful and know how to use them is a must for a SQL Developer.
select a.ID
, a.category
, a.creator
, a.curaatus
, a.town
, a.new
, a.pics
, a.galcount
, a.link
, a.crdate
, a.access
, a.new
, a.laamod
, case
when b.GDIR is not null -- it has a gallery record
or 'userlevel' >= a.access -- userlevel greater than sites access
then 1
else 0
end as allow
from sites a
outer apply (select top 1 *
from gallerys b
where b.GDIR = a.ID
and b.gcreator = 'pacman'
order by b.GDIR
) b
where a.closea = 'toronto'
and a.province = 'Ontario'
and (a.fm2<>'Y' or a.fm2 is null) -
I did. However the string above is pretty intensive for someone not used to Join commands.
Thank you for this code, it works!
I then display each location for that city... there are roughly 5-200 entries per city. It's not a CPU intensive process. So I'd display each of the above entries for a user.Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply