[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Help on easy Join-Clause needed

Fritz

3/18/2007 10:41:00 PM

hi,

i got 2 tables called dvdinfo and actor. inside dvdinfo one can find i.e.
titel, director, actorid1, actorid2, actorid3...

inside actor i have added actorid and actor. actorid is thought as 1:n
relationship to dvdinfo.actorid<x>.

i know want to see an output as following:

select titel, director, actor1, actor2, actor3 from dvdinfo, actor, which
could end as outut i.e. "ocean's eleven, steven soderbergh, george clooney,
brad pitt, matt damon".

my select-clause is obviously wrong:

SELECT DVDInfo.Titel, DVDInfo.Director, Actors.Actor as Actor1
FROM Actors INNER JOIN DVDInfo ON Actors.ActorId = DVDInfo.ActorId1,
Actors.Actor as Actor2 FROM Actors INNER JOIN DVDInfo ON Actors.ActorId =
DVDInfo.ActorId2, Actors.Actor as Actor3 FROM Actors INNER JOIN DVDInfo ON
Actors.ActorId = DVDInfo.ActorId3;

what would be the right one? or do i have to change the layout of my tables?

thanks in advance, fritz
--
Fritz Theiss
12 Answers

Sylvain Lafontaine

3/18/2007 11:23:00 PM

0

You can have multiple key in a single relationship:

... ON (Actors.ActorId = DVDInfo.ActorId1 or Actors.ActorId =
DVDInfo.ActorId2 or ...)

Using an alias will help you writing (and understanding) this stuff:

FROM Actors as A INNER JOIN DVDInfo D ON (A.ActorId = D.ActorId1 or
A.ActorId = D.ActorId2 or ...)

In your case, the best solution would be to redesign the layout of your
tables. You are in a situation where one DVD might have one or more Actor
and one Actor be part of more than one DVD, this is called a N:N
relationship and is done by creating/using a third table to maintain the
relationship between the first two tables. This table will contains one
DVDId and one ActorID by row. You can use these two fields as the primary
key of your table or if you are using things like Access as the interface,
add a third field with an identity value to act as an independant primary
key. (This third field is not mandatory but will save you many problems
with things like Access ADP.)

Whether or not the fact that you will be using the two ID fields as the
primary key, probably that it won't be a bad idea to add a second unique
index with these two fields reversed in order to increase retrieval
performance. If you want to have a clustered index, the best choice would
be to use any of these indexes that will begin with the DVDId field because
you are more likely to add a new DVD and possibly reuse one or more existing
actor than to create an actor and add his name to a collection of already
created DVD records.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Fritz" <Fritz@discussions.microsoft.com> wrote in message
news:84EF72F8-81D4-4A18-9970-EDECABCC2DFD@microsoft.com...
> hi,
>
> i got 2 tables called dvdinfo and actor. inside dvdinfo one can find i.e.
> titel, director, actorid1, actorid2, actorid3...
>
> inside actor i have added actorid and actor. actorid is thought as 1:n
> relationship to dvdinfo.actorid<x>.
>
> i know want to see an output as following:
>
> select titel, director, actor1, actor2, actor3 from dvdinfo, actor, which
> could end as outut i.e. "ocean's eleven, steven soderbergh, george
> clooney,
> brad pitt, matt damon".
>
> my select-clause is obviously wrong:
>
> SELECT DVDInfo.Titel, DVDInfo.Director, Actors.Actor as Actor1
> FROM Actors INNER JOIN DVDInfo ON Actors.ActorId = DVDInfo.ActorId1,
> Actors.Actor as Actor2 FROM Actors INNER JOIN DVDInfo ON Actors.ActorId =
> DVDInfo.ActorId2, Actors.Actor as Actor3 FROM Actors INNER JOIN DVDInfo ON
> Actors.ActorId = DVDInfo.ActorId3;
>
> what would be the right one? or do i have to change the layout of my
> tables?
>
> thanks in advance, fritz
> --
> Fritz Theiss


Fritz

3/19/2007 8:51:00 AM

0

Thanks, Sylvain - this sounds good.

Indeed I am using Access :-(. So I am wondering what ADP means...

Thanks again, Fritz
--
Fritz Theiss




"Sylvain Lafontaine" wrote:

> You can have multiple key in a single relationship:
>
> ... ON (Actors.ActorId = DVDInfo.ActorId1 or Actors.ActorId =
> DVDInfo.ActorId2 or ...)
>
> Using an alias will help you writing (and understanding) this stuff:
>
> FROM Actors as A INNER JOIN DVDInfo D ON (A.ActorId = D.ActorId1 or
> A.ActorId = D.ActorId2 or ...)
>
> In your case, the best solution would be to redesign the layout of your
> tables. You are in a situation where one DVD might have one or more Actor
> and one Actor be part of more than one DVD, this is called a N:N
> relationship and is done by creating/using a third table to maintain the
> relationship between the first two tables. This table will contains one
> DVDId and one ActorID by row. You can use these two fields as the primary
> key of your table or if you are using things like Access as the interface,
> add a third field with an identity value to act as an independant primary
> key. (This third field is not mandatory but will save you many problems
> with things like Access ADP.)
>
> Whether or not the fact that you will be using the two ID fields as the
> primary key, probably that it won't be a bad idea to add a second unique
> index with these two fields reversed in order to increase retrieval
> performance. If you want to have a clustered index, the best choice would
> be to use any of these indexes that will begin with the DVDId field because
> you are more likely to add a new DVD and possibly reuse one or more existing
> actor than to create an actor and add his name to a collection of already
> created DVD records.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Fritz" <Fritz@discussions.microsoft.com> wrote in message
> news:84EF72F8-81D4-4A18-9970-EDECABCC2DFD@microsoft.com...
> > hi,
> >
> > i got 2 tables called dvdinfo and actor. inside dvdinfo one can find i.e.
> > titel, director, actorid1, actorid2, actorid3...
> >
> > inside actor i have added actorid and actor. actorid is thought as 1:n
> > relationship to dvdinfo.actorid<x>.
> >
> > i know want to see an output as following:
> >
> > select titel, director, actor1, actor2, actor3 from dvdinfo, actor, which
> > could end as outut i.e. "ocean's eleven, steven soderbergh, george
> > clooney,
> > brad pitt, matt damon".
> >
> > my select-clause is obviously wrong:
> >
> > SELECT DVDInfo.Titel, DVDInfo.Director, Actors.Actor as Actor1
> > FROM Actors INNER JOIN DVDInfo ON Actors.ActorId = DVDInfo.ActorId1,
> > Actors.Actor as Actor2 FROM Actors INNER JOIN DVDInfo ON Actors.ActorId =
> > DVDInfo.ActorId2, Actors.Actor as Actor3 FROM Actors INNER JOIN DVDInfo ON
> > Actors.ActorId = DVDInfo.ActorId3;
> >
> > what would be the right one? or do i have to change the layout of my
> > tables?
> >
> > thanks in advance, fritz
> > --
> > Fritz Theiss
>
>
>

Sylvain Lafontaine

3/19/2007 4:10:00 PM

0

ADP: Active Data Project, the other method for accessing a SQL-Server from
Access; the first one being the ODBC linked tables/sql passthrough queries
and the third one using independant connections/recordsets with either DAO
or ADO.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Fritz" <Fritz@discussions.microsoft.com> wrote in message
news:66DE5477-7345-4EBC-BE90-867D0FF0F93A@microsoft.com...
> Thanks, Sylvain - this sounds good.
>
> Indeed I am using Access :-(. So I am wondering what ADP means...
>
> Thanks again, Fritz
> --
> Fritz Theiss
>
>
>
>
> "Sylvain Lafontaine" wrote:
>
>> You can have multiple key in a single relationship:
>>
>> ... ON (Actors.ActorId = DVDInfo.ActorId1 or Actors.ActorId =
>> DVDInfo.ActorId2 or ...)
>>
>> Using an alias will help you writing (and understanding) this stuff:
>>
>> FROM Actors as A INNER JOIN DVDInfo D ON (A.ActorId = D.ActorId1 or
>> A.ActorId = D.ActorId2 or ...)
>>
>> In your case, the best solution would be to redesign the layout of your
>> tables. You are in a situation where one DVD might have one or more
>> Actor
>> and one Actor be part of more than one DVD, this is called a N:N
>> relationship and is done by creating/using a third table to maintain the
>> relationship between the first two tables. This table will contains one
>> DVDId and one ActorID by row. You can use these two fields as the
>> primary
>> key of your table or if you are using things like Access as the
>> interface,
>> add a third field with an identity value to act as an independant primary
>> key. (This third field is not mandatory but will save you many problems
>> with things like Access ADP.)
>>
>> Whether or not the fact that you will be using the two ID fields as the
>> primary key, probably that it won't be a bad idea to add a second unique
>> index with these two fields reversed in order to increase retrieval
>> performance. If you want to have a clustered index, the best choice
>> would
>> be to use any of these indexes that will begin with the DVDId field
>> because
>> you are more likely to add a new DVD and possibly reuse one or more
>> existing
>> actor than to create an actor and add his name to a collection of already
>> created DVD records.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "Fritz" <Fritz@discussions.microsoft.com> wrote in message
>> news:84EF72F8-81D4-4A18-9970-EDECABCC2DFD@microsoft.com...
>> > hi,
>> >
>> > i got 2 tables called dvdinfo and actor. inside dvdinfo one can find
>> > i.e.
>> > titel, director, actorid1, actorid2, actorid3...
>> >
>> > inside actor i have added actorid and actor. actorid is thought as 1:n
>> > relationship to dvdinfo.actorid<x>.
>> >
>> > i know want to see an output as following:
>> >
>> > select titel, director, actor1, actor2, actor3 from dvdinfo, actor,
>> > which
>> > could end as outut i.e. "ocean's eleven, steven soderbergh, george
>> > clooney,
>> > brad pitt, matt damon".
>> >
>> > my select-clause is obviously wrong:
>> >
>> > SELECT DVDInfo.Titel, DVDInfo.Director, Actors.Actor as Actor1
>> > FROM Actors INNER JOIN DVDInfo ON Actors.ActorId = DVDInfo.ActorId1,
>> > Actors.Actor as Actor2 FROM Actors INNER JOIN DVDInfo ON Actors.ActorId
>> > =
>> > DVDInfo.ActorId2, Actors.Actor as Actor3 FROM Actors INNER JOIN DVDInfo
>> > ON
>> > Actors.ActorId = DVDInfo.ActorId3;
>> >
>> > what would be the right one? or do i have to change the layout of my
>> > tables?
>> >
>> > thanks in advance, fritz
>> > --
>> > Fritz Theiss
>>
>>
>>


Fritz Theiss

3/19/2007 9:39:00 PM

0

Thanks again.

However I have created a third table called ActorDVDInfo with a primar key
beside of the other two fields ActorId and DVDInfoId, which "point" to my
tables in question.

But when creating my Join-Clause it still fails. Can you please let me know
the appropriate T-SQL?

--
Fritz Theiss


"Sylvain Lafontaine" wrote:

> ADP: Active Data Project, the other method for accessing a SQL-Server from
> Access; the first one being the ODBC linked tables/sql passthrough queries
> and the third one using independant connections/recordsets with either DAO
> or ADO.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Fritz" <Fritz@discussions.microsoft.com> wrote in message
> news:66DE5477-7345-4EBC-BE90-867D0FF0F93A@microsoft.com...
> > Thanks, Sylvain - this sounds good.
> >
> > Indeed I am using Access :-(. So I am wondering what ADP means...
> >
> > Thanks again, Fritz
> > --
> > Fritz Theiss
> >
> >
> >
> >
> > "Sylvain Lafontaine" wrote:
> >
> >> You can have multiple key in a single relationship:
> >>
> >> ... ON (Actors.ActorId = DVDInfo.ActorId1 or Actors.ActorId =
> >> DVDInfo.ActorId2 or ...)
> >>
> >> Using an alias will help you writing (and understanding) this stuff:
> >>
> >> FROM Actors as A INNER JOIN DVDInfo D ON (A.ActorId = D.ActorId1 or
> >> A.ActorId = D.ActorId2 or ...)
> >>
> >> In your case, the best solution would be to redesign the layout of your
> >> tables. You are in a situation where one DVD might have one or more
> >> Actor
> >> and one Actor be part of more than one DVD, this is called a N:N
> >> relationship and is done by creating/using a third table to maintain the
> >> relationship between the first two tables. This table will contains one
> >> DVDId and one ActorID by row. You can use these two fields as the
> >> primary
> >> key of your table or if you are using things like Access as the
> >> interface,
> >> add a third field with an identity value to act as an independant primary
> >> key. (This third field is not mandatory but will save you many problems
> >> with things like Access ADP.)
> >>
> >> Whether or not the fact that you will be using the two ID fields as the
> >> primary key, probably that it won't be a bad idea to add a second unique
> >> index with these two fields reversed in order to increase retrieval
> >> performance. If you want to have a clustered index, the best choice
> >> would
> >> be to use any of these indexes that will begin with the DVDId field
> >> because
> >> you are more likely to add a new DVD and possibly reuse one or more
> >> existing
> >> actor than to create an actor and add his name to a collection of already
> >> created DVD records.
> >>
> >> --
> >> Sylvain Lafontaine, ing.
> >> MVP - Technologies Virtual-PC
> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
> >>
> >>
> >> "Fritz" <Fritz@discussions.microsoft.com> wrote in message
> >> news:84EF72F8-81D4-4A18-9970-EDECABCC2DFD@microsoft.com...
> >> > hi,
> >> >
> >> > i got 2 tables called dvdinfo and actor. inside dvdinfo one can find
> >> > i.e.
> >> > titel, director, actorid1, actorid2, actorid3...
> >> >
> >> > inside actor i have added actorid and actor. actorid is thought as 1:n
> >> > relationship to dvdinfo.actorid<x>.
> >> >
> >> > i know want to see an output as following:
> >> >
> >> > select titel, director, actor1, actor2, actor3 from dvdinfo, actor,
> >> > which
> >> > could end as outut i.e. "ocean's eleven, steven soderbergh, george
> >> > clooney,
> >> > brad pitt, matt damon".
> >> >
> >> > my select-clause is obviously wrong:
> >> >
> >> > SELECT DVDInfo.Titel, DVDInfo.Director, Actors.Actor as Actor1
> >> > FROM Actors INNER JOIN DVDInfo ON Actors.ActorId = DVDInfo.ActorId1,
> >> > Actors.Actor as Actor2 FROM Actors INNER JOIN DVDInfo ON Actors.ActorId
> >> > =
> >> > DVDInfo.ActorId2, Actors.Actor as Actor3 FROM Actors INNER JOIN DVDInfo
> >> > ON
> >> > Actors.ActorId = DVDInfo.ActorId3;
> >> >
> >> > what would be the right one? or do i have to change the layout of my
> >> > tables?
> >> >
> >> > thanks in advance, fritz
> >> > --
> >> > Fritz Theiss
> >>
> >>
> >>
>
>
>

Hugo Kornelis

3/19/2007 10:21:00 PM

0

On Mon, 19 Mar 2007 14:39:18 -0700, Fritz Theiss wrote:

>Thanks again.
>
>However I have created a third table called ActorDVDInfo with a primar key
>beside of the other two fields ActorId and DVDInfoId, which "point" to my
>tables in question.
>
>But when creating my Join-Clause it still fails. Can you please let me know
>the appropriate T-SQL?

Hi Fritz,

Forgive me if I get some of the table or column names wrong; the query
should be something such as

SELECT d.Title, d.Director, a.Actor
FROM DVDs AS d
INNER JOIN ActorDVDInfo AS ad
ON ad.DVDid = d.DVDid
INNER JOIN Actors AS a
ON a.ActorID = ad.ActorID;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Fritz Theiss

3/19/2007 11:16:00 PM

0

Hi Hugo,

thanks for your answer.

I modified your query accordingly as following:

SELECT d.Title, d.Director, a.Actor
FROM DVDs AS d
INNER JOIN ActorDVDInfo AS ad
ON ad.DVDid = d.DVDid
INNER JOIN Actors AS a
ON a.ActorID = ad.ActorID;

However I now receive an error:

Syntax Error (missing operator) in query 'ad.DVDInfoId = d.DVDInfoId INNER
JOIN Actor as a ON a.ActorId = ad.ActorId'.

What is wrong here?

However I am also wondering whether this query would show ALL MY ACTORS (see
my layout as described in the beginning of this thread)?


--
Fritz Theiss



"Hugo Kornelis" wrote:

> On Mon, 19 Mar 2007 14:39:18 -0700, Fritz Theiss wrote:
>
> >Thanks again.
> >
> >However I have created a third table called ActorDVDInfo with a primar key
> >beside of the other two fields ActorId and DVDInfoId, which "point" to my
> >tables in question.
> >
> >But when creating my Join-Clause it still fails. Can you please let me know
> >the appropriate T-SQL?
>
> Hi Fritz,
>
> Forgive me if I get some of the table or column names wrong; the query
> should be something such as
>
> SELECT d.Title, d.Director, a.Actor
> FROM DVDs AS d
> INNER JOIN ActorDVDInfo AS ad
> ON ad.DVDid = d.DVDid
> INNER JOIN Actors AS a
> ON a.ActorID = ad.ActorID;
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...
>

Sylvain Lafontaine

3/20/2007 1:33:00 AM

0

It's because you are using Access and that the syntax for multiple joins in
Access is slightly different from the T-SQL that you can find on SQL-Server:
you must add a couple of parenthesis here and there around your joins. I
suggest that you use the Graphical Query Tool (don't remember the exact
name) in Access to get a taste for the proper placement of the required
parenthesis.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Fritz Theiss" <FritzTheiss@discussions.microsoft.com> wrote in message
news:339E5C98-F827-44C9-8758-247A29CE4F29@microsoft.com...
> Hi Hugo,
>
> thanks for your answer.
>
> I modified your query accordingly as following:
>
> SELECT d.Title, d.Director, a.Actor
> FROM DVDs AS d
> INNER JOIN ActorDVDInfo AS ad
> ON ad.DVDid = d.DVDid
> INNER JOIN Actors AS a
> ON a.ActorID = ad.ActorID;
>
> However I now receive an error:
>
> Syntax Error (missing operator) in query 'ad.DVDInfoId = d.DVDInfoId INNER
> JOIN Actor as a ON a.ActorId = ad.ActorId'.
>
> What is wrong here?
>
> However I am also wondering whether this query would show ALL MY ACTORS
> (see
> my layout as described in the beginning of this thread)?
>
>
> --
> Fritz Theiss
>
>
>
> "Hugo Kornelis" wrote:
>
>> On Mon, 19 Mar 2007 14:39:18 -0700, Fritz Theiss wrote:
>>
>> >Thanks again.
>> >
>> >However I have created a third table called ActorDVDInfo with a primar
>> >key
>> >beside of the other two fields ActorId and DVDInfoId, which "point" to
>> >my
>> >tables in question.
>> >
>> >But when creating my Join-Clause it still fails. Can you please let me
>> >know
>> >the appropriate T-SQL?
>>
>> Hi Fritz,
>>
>> Forgive me if I get some of the table or column names wrong; the query
>> should be something such as
>>
>> SELECT d.Title, d.Director, a.Actor
>> FROM DVDs AS d
>> INNER JOIN ActorDVDInfo AS ad
>> ON ad.DVDid = d.DVDid
>> INNER JOIN Actors AS a
>> ON a.ActorID = ad.ActorID;
>>
>> --
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog: http://sqlblog.com/blogs/hug...
>>


Hugo Kornelis

3/20/2007 6:24:00 PM

0

On Mon, 19 Mar 2007 16:16:05 -0700, Fritz Theiss wrote:

>Hi Hugo,
>
>thanks for your answer.
>
>I modified your query accordingly as following:
>
>SELECT d.Title, d.Director, a.Actor
>FROM DVDs AS d
>INNER JOIN ActorDVDInfo AS ad
> ON ad.DVDid = d.DVDid
>INNER JOIN Actors AS a
> ON a.ActorID = ad.ActorID;
>
>However I now receive an error:
>
>Syntax Error (missing operator) in query 'ad.DVDInfoId = d.DVDInfoId INNER
>JOIN Actor as a ON a.ActorId = ad.ActorId'.
>
>What is wrong here?

Hi Fritz,

I don't understand. I was unable to reproduce - I copied the query above
in a query window and, after creating the tables, it ran fine.

Also (that's why I don't understand it), the query fragment in the error
message you quote is different from the modified query you posted, so I
fail to see where this comes from.

If you post the EXACT query (use copy and paste!!) and the EXACT error
message (again, use copy and paste), I might be able to help you
further. It would also help if you could post the CREATE TABLE
statements used to make your tables, plus some INSERT statements with a
few rows of sample data.

Finally, just a thoought - make sure yoou have not selected a part of
your query when clicking the "Execute" button. If a part of the query
window is selected, SQL Server will only execute that part; if that is a
partial query, you can expect errors to occur.

>However I am also wondering whether this query would show ALL MY ACTORS (see
>my layout as described in the beginning of this thread)?

This query SHOULD show all your actors, one per row. So you should
expect to get something like this:

Title Director Actor
ocean's eleven steven soderbergh george clooney
ocean's eleven steven soderbergh brad pitt
ocean's eleven steven soderbergh matt damon
(...)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Fritz Theiss

3/20/2007 8:23:00 PM

0

Hi Hugo,

you are right. I now see the same output as you do:

Title Director Actor
ocean's eleven steven soderbergh george clooney
ocean's eleven steven soderbergh brad pitt
ocean's eleven steven soderbergh matt damon

My query is now the following (I changed my layout a little bit):

SELECT d.Title, d.Director, a.ActorName
FROM (DVD AS d INNER JOIN ActorDVD AS ad ON ad.DVDId=d.DVDId)
INNER JOIN Actor AS a ON a.ActorID=ad.ActorID;

My last question would be: Is it possible to create a view as following:

Title Director Actor1
Actor2 Actor3
ocean's eleven steven soderbergh george clooney brad pitt matt
damon
pretty woman gary marshall richard gere julia roberts

If yes, how?

Thanks again, Fritz
--
Fritz Theiss


"Hugo Kornelis" wrote:

> On Mon, 19 Mar 2007 16:16:05 -0700, Fritz Theiss wrote:
>
> >Hi Hugo,
> >
> >thanks for your answer.
> >
> >I modified your query accordingly as following:
> >
> >SELECT d.Title, d.Director, a.Actor
> >FROM DVDs AS d
> >INNER JOIN ActorDVDInfo AS ad
> > ON ad.DVDid = d.DVDid
> >INNER JOIN Actors AS a
> > ON a.ActorID = ad.ActorID;
> >
> >However I now receive an error:
> >
> >Syntax Error (missing operator) in query 'ad.DVDInfoId = d.DVDInfoId INNER
> >JOIN Actor as a ON a.ActorId = ad.ActorId'.
> >
> >What is wrong here?
>
> Hi Fritz,
>
> I don't understand. I was unable to reproduce - I copied the query above
> in a query window and, after creating the tables, it ran fine.
>
> Also (that's why I don't understand it), the query fragment in the error
> message you quote is different from the modified query you posted, so I
> fail to see where this comes from.
>
> If you post the EXACT query (use copy and paste!!) and the EXACT error
> message (again, use copy and paste), I might be able to help you
> further. It would also help if you could post the CREATE TABLE
> statements used to make your tables, plus some INSERT statements with a
> few rows of sample data.
>
> Finally, just a thoought - make sure yoou have not selected a part of
> your query when clicking the "Execute" button. If a part of the query
> window is selected, SQL Server will only execute that part; if that is a
> partial query, you can expect errors to occur.
>
> >However I am also wondering whether this query would show ALL MY ACTORS (see
> >my layout as described in the beginning of this thread)?
>
> This query SHOULD show all your actors, one per row. So you should
> expect to get something like this:
>
> Title Director Actor
> ocean's eleven steven soderbergh george clooney
> ocean's eleven steven soderbergh brad pitt
> ocean's eleven steven soderbergh matt damon
> (...)
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...
>

Hugo Kornelis

3/20/2007 11:56:00 PM

0

On Tue, 20 Mar 2007 13:23:18 -0700, Fritz Theiss wrote:

>Hi Hugo,
>
>you are right. I now see the same output as you do:
>
>Title Director Actor
>ocean's eleven steven soderbergh george clooney
>ocean's eleven steven soderbergh brad pitt
>ocean's eleven steven soderbergh matt damon
>
>My query is now the following (I changed my layout a little bit):
>
>SELECT d.Title, d.Director, a.ActorName
>FROM (DVD AS d INNER JOIN ActorDVD AS ad ON ad.DVDId=d.DVDId)
>INNER JOIN Actor AS a ON a.ActorID=ad.ActorID;
>
>My last question would be: Is it possible to create a view as following:
>
>Title Director Actor1
>Actor2 Actor3
>ocean's eleven steven soderbergh george clooney brad pitt matt
>damon
>pretty woman gary marshall richard gere julia roberts
>
>If yes, how?
>
>Thanks again, Fritz

Hi Fritz,

That is really a presentation issue, which is best handled in the
presentation layer (i.e. the client). If you really HAVE to do it on the
server, you'll have no choice but to use a slow and ugly kludge.

Exactly how slow and ugly depends on the version of SQL Server you're
using. It also depends on the logic that determines which actor will be
in the Actor1 column, which in Actor2, etc.

If you must do this server-side, than answer the questions above. But as
I said before, best would be to handle this client-side.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...