How to encrypt the URL

I want to pass some information in the URL to Reporting Service.
But the Url can be view clearly.
But there are some sensitive information
How can I encrypt the URL?

I don't know if this would be possible for you, but if you render thereport using ASP.Net, you can pass variables to the report by settingvariables in cookies. This way, you show the report the way that itneeds to be, and the client has no access to the information. I have anexample if you would be interested.
|||

Thanks for your answer.

I need your examples.

Please give me!

|||

Hi,
Can I also have the example. I am also currently looking into how not to show the url.
Thanks.

|||Ok, well, first, you need to add a parameter to your report. When youtest run the report, it will ask you for the value of your prompt.Then, in ASP.Net (using VB.Net), this is how I render to an .aspx page:
<code>
CurrentLocation = Request.Cookies("LocationCookie").Value
Dim rs As New newafp.RSService.ReportingService
rs.Credentials = New System.Net.NetworkCredential("UserName", "Password", "")
Dim parameters(0) As RSService.ParameterValue
parameters(0) = New RSService.ParameterValue
parameters(0).Name = "Location"
parameters(0).Value = CurrentLocation
Dim results As Byte(), image As Byte()
Dim streamids As String(), streamid As String
results =rs.Render("/Folder/ReportName", "HTML4.0", Nothing,"<DeviceInfo><HTMLFragment>True</HTMLFragment><StreamRoot>/Reports/</StreamRoot></DeviceInfo>",parameters, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,streamids)
For Each streamid In streamids
image = rs.RenderStream("/Folder/ReportName", "HTML4.0", streamid, _
Nothing, Nothing, parameters, Nothing, Nothing)
Dim stream As System.IO.FileStream = _
System.IO.File.OpenWrite(Server.MapPath("") & "\" & streamid)
stream.Write(image, 0, CInt(image.Length))
stream.Close()
Next
Response.BinaryWrite(results)
</code>
I don't know if you see it or not, but I have an image that isdisplayed with this report. You don't need it, but I kept it in becauseit will still work even if you don't have any images to write. Or, ifyou don't want to have it included, you can remove the For Loop (fromFor Each line to the Next line). Let me know if you have any questionson this.
|||Thanks,
But there are some statements which I can't understand.
I will study with these codes
Thank again.sql

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
What is a better solution?
We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegro ups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
>
> What is a better solution?
>

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'),
0) = 1)
What is a better solution?We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'),
0) = 1)
>
> What is a better solution?
>|||I see there is a way to do this by adding the user to the
TargetServersRole in MSDB, although it has a downside. See here:
http://www.mcse.ms/message638764.html
Daniel Jameson wrote:
[vbcol=seagreen]
> We got around this by using a queue to disconnect the security.
> 1) create a queue table that takes the name of a job and a status value.
> 2) create a stored procedure that can read the queue, find any pending job
s,
> run sp_startjob for any pending jobs, mark started jobs as complete.
> 3) create SQL Agent job owned by SA that runs once every minute and runs
> that stored procedure.
> 4) user inserts job name in queue and waits ~1 min.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
> "Laurence" <laurencen@.eurostop.co.uk> wrote in message
> news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...sql

How to enable a non-SysAdmin user to start a job

I thought I could do this by granting permission to a few MSDB
procedures:
grant execute on sp_help_jobhistory to UserRole -- view job history
grant execute on sp_help_job to UserRole -- view job
grant execute on sp_start_job to UserRole -- start job
but I get an error:
Server: Msg 14262, Level 16, State 1, Line 1
The specified @.job_name ('Name Of Job') does not exist.
I looked at the sysjobs_view in MSDB (below) it looks like only the
owner, SysAdmins and TargetServersRole (what is this?) can view the
jobs. If I alter this view to include my UserRole this allows them to
start the job, but I don't want to do this because it is a hack.
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
What is a better solution?We got around this by using a queue to disconnect the security.
1) create a queue table that takes the name of a job and a status value.
2) create a stored procedure that can read the queue, find any pending jobs,
run sp_startjob for any pending jobs, mark started jobs as complete.
3) create SQL Agent job owned by SA that runs once every minute and runs
that stored procedure.
4) user inserts job name in queue and waits ~1 min.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Laurence" <laurencen@.eurostop.co.uk> wrote in message
news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
>I thought I could do this by granting permission to a few MSDB
> procedures:
> grant execute on sp_help_jobhistory to UserRole -- view job history
> grant execute on sp_help_job to UserRole -- view job
> grant execute on sp_start_job to UserRole -- start job
> but I get an error:
> Server: Msg 14262, Level 16, State 1, Line 1
> The specified @.job_name ('Name Of Job') does not exist.
> I looked at the sysjobs_view in MSDB (below) it looks like only the
> owner, SysAdmins and TargetServersRole (what is this?) can view the
> jobs. If I alter this view to include my UserRole this allows them to
> start the job, but I don't want to do this because it is a hack.
> SELECT *
> FROM msdb.dbo.sysjobs
> WHERE (owner_sid = SUSER_SID())
> OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
>
> What is a better solution?
>|||I see there is a way to do this by adding the user to the
TargetServersRole in MSDB, although it has a downside. See here:
http://www.mcse.ms/message638764.html
Daniel Jameson wrote:
> We got around this by using a queue to disconnect the security.
> 1) create a queue table that takes the name of a job and a status value.
> 2) create a stored procedure that can read the queue, find any pending jobs,
> run sp_startjob for any pending jobs, mark started jobs as complete.
> 3) create SQL Agent job owned by SA that runs once every minute and runs
> that stored procedure.
> 4) user inserts job name in queue and waits ~1 min.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
> "Laurence" <laurencen@.eurostop.co.uk> wrote in message
> news:1161622953.615964.21000@.k70g2000cwa.googlegroups.com...
> >I thought I could do this by granting permission to a few MSDB
> > procedures:
> >
> > grant execute on sp_help_jobhistory to UserRole -- view job history
> > grant execute on sp_help_job to UserRole -- view job
> > grant execute on sp_start_job to UserRole -- start job
> >
> > but I get an error:
> >
> > Server: Msg 14262, Level 16, State 1, Line 1
> > The specified @.job_name ('Name Of Job') does not exist.
> >
> > I looked at the sysjobs_view in MSDB (below) it looks like only the
> > owner, SysAdmins and TargetServersRole (what is this?) can view the
> > jobs. If I alter this view to include my UserRole this allows them to
> > start the job, but I don't want to do this because it is a hack.
> >
> > SELECT *
> > FROM msdb.dbo.sysjobs
> > WHERE (owner_sid = SUSER_SID())
> > OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
> > OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
> >
> >
> > What is a better solution?
> >

how to Drop an orphan view

Hi...
I have this in SQL Server 2000:
select * from sysobjects where name = 'xView' -- returns a row
-- but if I exec this script:
select * from xView
-- return to me
Mens. 208, Levl 16...
Object 'xView' is not valid.
The problem is I need to drop the login xLogin who is the xView owner.
Hw i do? why occurs this....thanks in advance.
--
ChevyHi Chevy,
Does
select * from xLogin.xView
works?
If that works then you can drop the view or change its ownership using
sp_changeobjectowner. Then drop the login.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Chevy" wrote:
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Chevy,
There probably is an object named 'xView', but it is not a view. If you try
the following, you will get the same error.
use master
select * from sp_who
If you query the system table you should do:
select * from sysobjects where name = 'xView' and type = 'V'
Or use the INFORMATION_SCHEMA views.
RLF
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:B6935BD1-F6A2-4980-9521-A9E064CAABFA@.microsoft.com...
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Yes Ben, you are rigth...I was really blind...thanks
___________
Chevy
"Ben Nevarez" wrote:
> Hi Chevy,
> Does
> select * from xLogin.xView
> works?
> If that works then you can drop the view or change its ownership using
> sp_changeobjectowner. Then drop the login.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Chevy" wrote:
> > Hi...
> >
> > I have this in SQL Server 2000:
> >
> > select * from sysobjects where name = 'xView' -- returns a row
> >
> > -- but if I exec this script:
> >
> > select * from xView
> >
> > -- return to me
> >
> > Mens. 208, Levl 16...
> > Object 'xView' is not valid.
> >
> > The problem is I need to drop the login xLogin who is the xView owner.
> >
> > Hw i do? why occurs this....thanks in advance.
> >
> > --
> > Chevy

how to Drop an orphan view

Hi...
I have this in SQL Server 2000:
select * from sysobjects where name = 'xView' -- returns a row
-- but if I exec this script:
select * from xView
-- return to me
Mens. 208, Levl 16...
Object 'xView' is not valid.
The problem is I need to drop the login xLogin who is the xView owner.
Hw i do? why occurs this....thanks in advance.
Chevy
Hi Chevy,
Does
select * from xLogin.xView
works?
If that works then you can drop the view or change its ownership using
sp_changeobjectowner. Then drop the login.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Chevy" wrote:

> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy
|||Chevy,
There probably is an object named 'xView', but it is not a view. If you try
the following, you will get the same error.
use master
select * from sp_who
If you query the system table you should do:
select * from sysobjects where name = 'xView' and type = 'V'
Or use the INFORMATION_SCHEMA views.
RLF
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:B6935BD1-F6A2-4980-9521-A9E064CAABFA@.microsoft.com...
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy
|||Yes Ben, you are rigth...I was really blind...thanks
___________
Chevy
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Chevy,
> Does
> select * from xLogin.xView
> works?
> If that works then you can drop the view or change its ownership using
> sp_changeobjectowner. Then drop the login.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Chevy" wrote:

how to Drop an orphan view

Hi...
I have this in SQL Server 2000:
select * from sysobjects where name = 'xView' -- returns a row
-- but if I exec this script:
select * from xView
-- return to me
Mens. 208, Levl 16...
Object 'xView' is not valid.
The problem is I need to drop the login xLogin who is the xView owner.
Hw i do? why occurs this....thanks in advance.
ChevyHi Chevy,
Does
select * from xLogin.xView
works?
If that works then you can drop the view or change its ownership using
sp_changeobjectowner. Then drop the login.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Chevy" wrote:

> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Chevy,
There probably is an object named 'xView', but it is not a view. If you try
the following, you will get the same error.
use master
select * from sp_who
If you query the system table you should do:
select * from sysobjects where name = 'xView' and type = 'V'
Or use the INFORMATION_SCHEMA views.
RLF
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:B6935BD1-F6A2-4980-9521-A9E064CAABFA@.microsoft.com...
> Hi...
> I have this in SQL Server 2000:
> select * from sysobjects where name = 'xView' -- returns a row
> -- but if I exec this script:
> select * from xView
> -- return to me
> Mens. 208, Levl 16...
> Object 'xView' is not valid.
> The problem is I need to drop the login xLogin who is the xView owner.
> Hw i do? why occurs this....thanks in advance.
> --
> Chevy|||Yes Ben, you are rigth...I was really blind...thanks
___________
Chevy
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Chevy,
> Does
> select * from xLogin.xView
> works?
> If that works then you can drop the view or change its ownership using
> sp_changeobjectowner. Then drop the login.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Chevy" wrote:
>

How to do a JOIN statement for a table with 2, one-to-many relationships.

Hello,

I want to be able to view data from 3 tables using the JOIN statement, but
I'm not sure of how to do it. I think i don't know the syntax of the joins.I
imagine this is easy for the experienced - but Im not.

Allow me to explain:
I have 2 Tables: PERSON and SIGN

PERSON
--
PersonNo int (Primary Key)
Name varchar(50)
StarSign int
FavFood int

SIGN
--
StarSign int (Primary Key)
StarSignName varchar(50)

Relationship: SIGN has a one-to-many relationship with PERSON. The linking
field is called 'StarSign'.

Question 1:
I want to display all the peoples names, and their star sign (whether they
have one or not).
Answer 1:
SELECT PERSON.Name, SIGN.StarSignName
FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;

No problems there. But now I want to do the same thing, but have their
favourite food displayed as well. So an additional table is needed:

FOOD
--
FavFood int (Primary Key)
FavFoodName varchar(50)

Relationship: FOOD has a one-to-many relationship with PERSON. The linking
field is called 'FavFood'.

Question 2:
I want to display all the peoples names, their star signs (whether they
have one or not), and their favourite food (whether they have one or not).
Answer 1:
?

I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL
the rows from table PERSON will appear 'irrespective' of whether they have
related records in the other tables.

Jack.Since PERSON is on the many-side in both cases, it's easy. basically, this is
the case where you have several lookup values, each of which is optional.

SELECT PERSON.Name, SIGN.StarSignName
FROM PERSON LEFT JOIN
SIGN ON PERSON.StarSign = SIGN.StarSign
LEFT JOIN
FOOD ON PERSON.FavFood = FOOD.FavFood;

(presumably, this is hypothetical, and I don't need to mention table/field
naming issues)

On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <jacksmith@.nospam.co.uk>
wrote:

>Hello,
>I want to be able to view data from 3 tables using the JOIN statement, but
>I'm not sure of how to do it. I think i don't know the syntax of the joins.I
>imagine this is easy for the experienced - but Im not.
>Allow me to explain:
>I have 2 Tables: PERSON and SIGN
>PERSON
>--
>PersonNo int (Primary Key)
>Name varchar(50)
>StarSign int
>FavFood int
>SIGN
>--
>StarSign int (Primary Key)
>StarSignName varchar(50)
>Relationship: SIGN has a one-to-many relationship with PERSON. The linking
>field is called 'StarSign'.
>Question 1:
>I want to display all the peoples names, and their star sign (whether they
>have one or not).
>Answer 1:
>SELECT PERSON.Name, SIGN.StarSignName
>FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
>No problems there. But now I want to do the same thing, but have their
>favourite food displayed as well. So an additional table is needed:
>FOOD
>--
>FavFood int (Primary Key)
>FavFoodName varchar(50)
>Relationship: FOOD has a one-to-many relationship with PERSON. The linking
>field is called 'FavFood'.
>Question 2:
>I want to display all the peoples names, their star signs (whether they
>have one or not), and their favourite food (whether they have one or not).
>Answer 1:
>?
>I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL
>the rows from table PERSON will appear 'irrespective' of whether they have
>related records in the other tables.
>Jack.|||Thank-you! One thing though if possible - can you repost your solution, but
nest the brakets around the joins.

Final thanks...
Jack.

"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:4a54n11jpod53n6k9n3j401knm7d90419v@.4ax.com...
> Since PERSON is on the many-side in both cases, it's easy. basically,
> this is
> the case where you have several lookup values, each of which is optional.
> SELECT PERSON.Name, SIGN.StarSignName
> FROM PERSON LEFT JOIN
> SIGN ON PERSON.StarSign = SIGN.StarSign
> LEFT JOIN
> FOOD ON PERSON.FavFood = FOOD.FavFood;
> (presumably, this is hypothetical, and I don't need to mention table/field
> naming issues)
>
> On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <jacksmith@.nospam.co.uk>
> wrote:
>>Hello,
>>
>>I want to be able to view data from 3 tables using the JOIN statement, but
>>I'm not sure of how to do it. I think i don't know the syntax of the
>>joins.I
>>imagine this is easy for the experienced - but Im not.
>>
>>Allow me to explain:
>>I have 2 Tables: PERSON and SIGN
>>
>>PERSON
>>--
>>PersonNo int (Primary Key)
>>Name varchar(50)
>>StarSign int
>>FavFood int
>>
>>SIGN
>>--
>>StarSign int (Primary Key)
>>StarSignName varchar(50)
>>
>>Relationship: SIGN has a one-to-many relationship with PERSON. The linking
>>field is called 'StarSign'.
>>
>>Question 1:
>>I want to display all the peoples names, and their star sign (whether they
>>have one or not).
>>Answer 1:
>>SELECT PERSON.Name, SIGN.StarSignName
>>FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;
>>
>>No problems there. But now I want to do the same thing, but have their
>>favourite food displayed as well. So an additional table is needed:
>>
>>FOOD
>>--
>>FavFood int (Primary Key)
>>FavFoodName varchar(50)
>>
>>Relationship: FOOD has a one-to-many relationship with PERSON. The linking
>>field is called 'FavFood'.
>>
>>Question 2:
>>I want to display all the peoples names, their star signs (whether they
>>have one or not), and their favourite food (whether they have one or not).
>>Answer 1:
>>?
>>
>>I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so
>>ALL
>>the rows from table PERSON will appear 'irrespective' of whether they have
>>related records in the other tables.
>>
>>Jack.
>|||Jack Smith (jacksmith@.nospam.co.uk) writes:
> Thank-you! One thing though if possible - can you repost your solution,
> but nest the brakets around the joins.

I'd rather not...

Personally, I would write Steve's solution as:

SELECT P.Name, S.StarSignName, F.FoodName
FROM PERSON P
LEFT JOIN SIGN S ON P.StarSign = S.StarSign
LEFT JOIN FOOD F ON P.FavFood = F.Food;

You can add parentheses to your heart's content, but for this query
it would add more confusion than necessary.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp