Jump to report and stay in the same frame

We have a windows application that opens up a report via an URL link. This
works fine and the report is opened whithin the same frame that the link is
displayed in.
Next step is to jump to a second (and third) report and this time I can't
stay in the same frame...
I have tried the Jump to Url with "&rc:LinkTarget=XXX".
XXX = "_Self", "_Parent", "_ReportFrame", ...
Any ideas?The LinkTarget parameter is not propogated to child reports on a
drill-through. This causes the first drill-through to show up in the
specified frame but any further drill-throughs won't receive (and thus won't
use) that parameter.
This is a limitation we hope to address at some point but it wasn't in
RS2000 and won't make it into RS2005.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tomas Fagerström (Sweden)" <Tomas Fagerström
(Sweden)@.discussions.microsoft.com> wrote in message
news:58E90983-5A00-43D0-B5B2-204F08A7C632@.microsoft.com...
> We have a windows application that opens up a report via an URL link. This
> works fine and the report is opened whithin the same frame that the link
> is
> displayed in.
> Next step is to jump to a second (and third) report and this time I can't
> stay in the same frame...
> I have tried the Jump to Url with "&rc:LinkTarget=XXX".
> XXX = "_Self", "_Parent", "_ReportFrame", ...
> Any ideas?
>|||OK, thanks for your reply.
I hope this will be fixed in the near future... ;-)
/Tomas
"Donovan Smith [MSFT]" wrote:
> The LinkTarget parameter is not propogated to child reports on a
> drill-through. This causes the first drill-through to show up in the
> specified frame but any further drill-throughs won't receive (and thus won't
> use) that parameter.
> This is a limitation we hope to address at some point but it wasn't in
> RS2000 and won't make it into RS2005.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tomas Fagerström (Sweden)" <Tomas Fagerström
> (Sweden)@.discussions.microsoft.com> wrote in message
> news:58E90983-5A00-43D0-B5B2-204F08A7C632@.microsoft.com...
> > We have a windows application that opens up a report via an URL link. This
> > works fine and the report is opened whithin the same frame that the link
> > is
> > displayed in.
> >
> > Next step is to jump to a second (and third) report and this time I can't
> > stay in the same frame...
> >
> > I have tried the Jump to Url with "&rc:LinkTarget=XXX".
> >
> > XXX = "_Self", "_Parent", "_ReportFrame", ...
> >
> > Any ideas?
> >
> >
>
>|||Hi,
ok at last i saw some confirmation from a microsoft people that this doesnt
work.
drill-through - LinkTarget is propaged to first level only in 2000RS whereas
it is not even propaged to first level that mean LinkTarget is not totally
working in 2005RS. Now i understand why microsoft want people to move forward
to 2005 Sql server. if not providing new feature atleast it should support
exsisting features.. way to go..
"Donovan Smith [MSFT]" wrote:
> The LinkTarget parameter is not propogated to child reports on a
> drill-through. This causes the first drill-through to show up in the
> specified frame but any further drill-throughs won't receive (and thus won't
> use) that parameter.
> This is a limitation we hope to address at some point but it wasn't in
> RS2000 and won't make it into RS2005.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tomas Fagerström (Sweden)" <Tomas Fagerström
> (Sweden)@.discussions.microsoft.com> wrote in message
> news:58E90983-5A00-43D0-B5B2-204F08A7C632@.microsoft.com...
> > We have a windows application that opens up a report via an URL link. This
> > works fine and the report is opened whithin the same frame that the link
> > is
> > displayed in.
> >
> > Next step is to jump to a second (and third) report and this time I can't
> > stay in the same frame...
> >
> > I have tried the Jump to Url with "&rc:LinkTarget=XXX".
> >
> > XXX = "_Self", "_Parent", "_ReportFrame", ...
> >
> > Any ideas?
> >
> >
>
>sql

Joining two tables with repeating rows

Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules.

UserAccess table:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User2 1 1

Modules table:

ModuleID Description

1 Mod1

2 Mod2

3 Mod3

4 Mod4

5 Mod5

What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data from the Modules table to repeat 5 rows for each user. This is a sample of the output I am trying to get:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User1 3 Null

User1 4 Null

User1 5 Null

User2 1 1

User2 2 Null

User2 3 Null

User2 4 Null

User2 5 Null

I've tried all sorts of joins but haven't been able to get this to happen. Not sure if this is possible? Thanks!!!

The magic words you want here are "CROSS JOIN". Previously known as 'comma'.

First make yourself a table of users. If you have this in a separate table already, then great. If you don't, slap yourself, write a post-it note to do it later, and make do with:

select *
from
(select distinct UserID from UserAccess) u

Now do your cross join to the modules table.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m

Note - there's no ON clause here... you want every possible combination.

Now join this to your UserAccess table to see if they have access or not. You'll want to use a LEFT JOIN to make sure you don't eliminate the rows you already have.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Now, ua will have null records for the times when there is no record to match (it's the way LEFT JOIN works). So you can just have a look to see if one of the records which can't be null (like userid) is null or not...

select u.UserID, m.ModuleID, case when ua.UserID is null then 1 else 0 end as AccessLevel

from

(select distinct UserID from UserAccess) u

cross join

Modules m

left join

UserAccess ua

on ua.moduleid = m.moduleid

and ua.userid = u.userid

Hope this works for you!

Rob|||

Part of the problem is that you need to have a users table. Then this becomes a bit easier of a task. So I built one in the query:

--sample tables (please include in future if you can...)
create table userAccess
(
userId varchar(8)
,moduleId int
,accessLevel int
,primary key (userId, moduleId)
)
insert into userAccess
select 'User1', 1, 1
union all
select 'User1', 2, 1
union all
select 'User2', 1, 1

create table modules
(
moduleId int
,description varchar(10)
,primary key (moduleId)
)
insert into modules
select 1, 'Mod1'
union all
select 2, 'Mod2'
union all
select 3, 'Mod3'
union all
select 4, 'Mod4'
union all
select 5, 'Mod5'

This query will get it for you:

select users.userId, modules.moduleId,userAccess.accessLevel
from (select distinct userId
from userAccess) as users --creates the users table with all users that have some access
cross join modules --cross join it with modules to get the all modules for all users set.

left outer join userAccess --then left join it to the access table to get the accessLevel column...
on userAccess.userId = users.userId
and modules.moduleId = userAccess.moduleId

|||Apparently I worked on my solution for at least 15 minutes :)|||15 minutes? Really?|||

Thank you both so much!!! I KNEW there was a way to do this but I couldn't quite get there. I do have a users table so luckily I do not have to slap myself.

Thanks again!!!

|||

You try to find out in

www.sqlzoo.com

Joining two tables multiple times

Hi
I have two tables (in an third party application, I cannot change the
data structure) as follows:-
T1 - Main data
Amount Ref1 Ref2 Ref3
==============================
100 A A A
150 A B A
200 A B B
T2 - Reference data
Type Value Name
=========================
Ref1 A Area 1
Ref1 B Area 2
...
Ref2 A Dept 1
Ref2 B Dept 2
...
Ref3 A Type 1
Ref3 B Type 3
At a simple level I want to be able to return (though obviously there
are many more complex applications of this data that I want to do):-
Amount Name1 Name2 Name3
==================================
100 Area 1 Dept 1 Type 1
150 Area 1 Dept 2 Type 1
200 Area 1 Dept 2 Type 2
At the moment I achive this by creating a query for each Refn type
Select * FROM T2
WHERE Type = 'Refn'
and then joining T1 several types to each of these queries. Is there a
way of creating this without creating the queries first?
App is SQL server, I'm using Access 2000 to query, but quite happy (and
permitted) to use passthrough queries instead.
Any help gratefully received!
MattTry this
create table #T1(Amount int, Ref1 char(1),Ref2 char(1),Ref3 char(1))
insert into #T1(Amount,Ref1,Ref2,Ref3) values(100,'A','A','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(150,'A','B','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(200,'A','B','B')
create table #T2(Type char(4),Value char(1), Name varchar(10))
insert into #T2(Type,Value,Name) values ('Ref1','A','Area 1')
insert into #T2(Type,Value,Name) values ('Ref1','B','Area 2')
insert into #T2(Type,Value,Name) values ('Ref2','A','Dept 1')
insert into #T2(Type,Value,Name) values ('Ref2','B','Dept 2')
insert into #T2(Type,Value,Name) values ('Ref3','A','Type 1')
insert into #T2(Type,Value,Name) values ('Ref3','B','Type 2')
select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
Name3
from #T1 as T1
inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
drop table #T1
drop table #T2|||markc600@.hotmail.com wrote:
> Try this
> [snipped]
> select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
> Name3
> from #T1 as T1
> inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
> inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
> inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
> drop table #T1
> drop table #T2
Mark
Many thanks for such a swift reply - just what I needed. Turned the
real world SQL into this (which worked a treat).
Matt
SELECT T1.CODE, T1.NAME AS COSTCENTRE, T2.NAME AS FUNCTION, T3.NAME AS
REGION, T4.NAME AS LNHREGION, T8.NAME AS BRANCH
FROM SADFLDGRIP AS DATA
INNER JOIN
SSRFACC AS CA ON
CA.SUN_DB = 'RIP'
AND
DATA.ACCNT_CODE = CA.ACCNT_CODE
INNER JOIN
SSRFANV AS T1 ON T1.CATEGORY = 'T1'
AND
T1.SUN_DB = 'RIP'
AND
DATA.ANAL_T1 = T1.CODE
INNER JOIN
SSRFANV AS T2 ON T2.CATEGORY = 'T2'
AND
T2.SUN_DB = 'RIP'
AND
DATA.ANAL_T2 = T2.CODE
INNER JOIN
SSRFANV AS T3 ON T3.CATEGORY = 'T3'
AND
T3.SUN_DB = 'RIP'
AND
DATA.ANAL_T3 = T3.CODE
INNER JOIN
SSRFANV AS T4 ON T4.CATEGORY = 'T4'
AND
T4.SUN_DB = 'RIP'
AND
DATA.ANAL_T4 = T4.CODE
INNER JOIN
SSRFANV AS T8 ON T8.CATEGORY = 'T8'
AND
T8.SUN_DB = 'RIP'
AND
DATA.ANAL_T8 = T8.CODE
WHERE DATA.PERIOD >= 2004001 AND DATA.PERIOD <=2005001 AND
CA.ACCNT_TYPE = 'P'
GROUP BY T1.CODE, T1.NAME, T2.NAME, T3.NAME, T4.NAME, T8.NAME
;|||you can try this 3 solutions
(i let myself redefine and rename some of your tables and columns):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='MainData') DROP TABLE MainData;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='RefData') DROP TABLE RefData;
CREATE TABLE MainData(
amnt INTEGER NOT NULL,
ref1 CHAR(1) NOT NULL CHECK(ref1 IN('A', 'B')),
ref2 CHAR(1) NOT NULL CHECK(ref2 IN('A', 'B')),
ref3 CHAR(1) NOT NULL CHECK(ref3 IN('A', 'B')));
CREATE INDEX MainData_ref1_Idx ON MainData(ref1);
CREATE INDEX MainData_ref2_Idx ON MainData(ref2);
CREATE INDEX MainData_ref3_Idx ON MainData(ref3);
INSERT INTO MainData
SELECT 100, 'A', 'A', 'A' UNION ALL
SELECT 150, 'A', 'B', 'A' UNION ALL
SELECT 200, 'A', 'B', 'B';
CREATE TABLE RefData(
ref_tp CHAR(4) NOT NULL,
ref_vl CHAR(1) NOT NULL CHECK(ref_vl IN('A', 'B')),
ref_nm CHAR(6) NOT NULL);
CREATE INDEX RefData_ref_tp_Idx ON RefData(ref_tp);
INSERT INTO RefData
SELECT 'Ref1','A','Area 1' UNION ALL
SELECT 'Ref1','B','Area 2' UNION ALL
SELECT 'Ref2','A','Dept 1' UNION ALL
SELECT 'Ref2','B','Dept 2' UNION ALL
SELECT 'Ref3','A','Type 1' UNION ALL
SELECT 'Ref3','B','Type 2';
-- amnt ref_nm1 ref_nm2 ref_nm3
-- 100 Area 1 Dept 1 Type 1
-- 150 Area 1 Dept 2 Type 1
-- 200 Area 1 Dept 2 Type 2
SELECT amnt,
(SELECT R1.ref_nm FROM RefData AS R1
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1) AS ref_nm1,
(SELECT R2.ref_nm FROM RefData AS R2
WHERE R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2) AS ref_nm2,
(SELECT R3.ref_nm FROM RefData AS R3
WHERE R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3) AS ref_nm3
FROM MainData AS M
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM MainData AS M, RefData AS R1, RefData AS R2, RefData AS R3
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1
AND R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2
AND R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM RefData AS R3
RIGHT OUTER JOIN RefData AS R2
RIGHT OUTER JOIN RefData AS R1
RIGHT OUTER JOIN MainData AS M
ON R1.ref_vl = M.ref1 AND R1.ref_tp = 'Ref1'
ON R2.ref_vl = M.ref2 AND R2.ref_tp = 'Ref2'
ON R3.ref_vl = M.ref3 AND R3.ref_tp = 'Ref3'
-- WHERE R1.ref_tp = 'Ref1'
-- AND R2.ref_tp = 'Ref2'
-- AND R3.ref_tp = 'Ref3'

Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:

> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated

Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated :)
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)

Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"hsifelbmur" wrote:

> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>
|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegr oups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
sql

Joining one database table with other database table in stroed procedures.

Hi All,
I need to perform a join on table1 of database1 with table2 or database2, in a stored procedure and return to my web application.. For this I'm providing execute permission on stored procedure, in database1 and "SELECT" permission on database2 table 2, to my database webuser.
Is there any way ( Another stored procedure in database2), i can get join on two tables without SELECT permission right on table2 ( or table1).
Purely using stored procedures. If so how?Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||Hi Paul,
Thanks, What is meant by security path? I didnot get ur point.
User have only public permission on both databases. Is it not enough? I do have execute permission on sp1 (Stored Procedure) in database1.
but in sp1 I have a select statement which is a join on database2 table-'table2'. When I tried to execute sp1 from web application only with execute permission on sp1-
I got an error as Webuser does not have 'SELECT' permission on table2 of database2. So I provided it and sp1 worked fine. But I don't want to use a SELECT permission- What is alternate, for the join.
sp1 has simple
SELECT *
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.col1 = t2.col1)

Originally posted by Paul Young
Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||by security path I was refering to how a user gets authenticated on server2 when making a connection from server1.

You might try using OPENQUERY to call a stored procedure on server2 and use the results to join to a table on server1. I haven't had the need for this in the past so I am working on theory here. Check BOL for usage on OPENQUERY, they have some good examples.|||When calling a stored proc from database 1, you need execute privs on the proc. You do not need to give the user privs on the table itself as long as it is in database 1, too. If database 2 is owned by the same user that is the owner of database 1, you do not need explicit permissions on the object in table 2 accessed by the proc. However, if the database owners are different for the two databases, the user calling the proc must have explicit permissions in the second database.

If need be, you can change the database owner by:

EXEC sp_changedbowner 'username'

Execute this in the database you want to change.|||Well Both databases are on same server. Is OPENQUERY solve in this case too?|||Originally posted by soumyag
Well Both databases are on same server.

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.|||Hi,
Both are on same server, but owned by different users. And I don't have any right to change the dbowners But how it will help in writing a join on tables. What is other alternative way to solve this problem.
Thanks.

Originally posted by bglass

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.