SQL Ignorance

[Alan Forsyth]

I've been asked by a friend to help with his Movie database. It's an
informal home user record of his own movie watching history.
He's asked me to write a particular query, and I'm stumped.

I have two questions.

He has tblMovie and tblActor (and others.). They're Joined with
tblMovieActor, because one Movie can have many Actors and one actor can
have many Movies. He doesn't have a record for all Actors in each movie
but does just his favourites. Other Actors in the Movie will be in a block
of text in the IMDBInfo Memo field.

Here's part of his structure.

tblMovie
MovieID (PK)
Title Txt
IMDBInfo Memo
.....More Fields.

tblActor
ActorID (PK)
FullName Txt (e.g. Clint Eastwood. In his database, NameFirst and
NameLast.)
.... More Fields

The Join Table
tblMovieActor
MovieActorID (PK)
MovieID Long
ActorID Long
Character Text
------------------------

This works well for him with a Query such as this:
-------- sql ------------
SELECT
tblActor.FullName
, tblMovieActor.Character
, tblMovie.Title
, tblMovie.IMDBInfo
FROM
tblMovie
RIGHT JOIN
(tblActor
RIGHT JOIN
tblMovieActor
ON
tblActor.ActorID = tblMovieActor.ActorID)
ON
tblMovie.MovieID = tblMovieActor.MovieID;
------- /sql ------------

He can use this WHERE Clause:

---Part sql ------------
WHERE
tblActor.ActorID In (
SELECT tblMovieActor.ActorID
FROM tblMovieActor
GROUP BY tblMovieActor.ActorID
HAVING Count(tblMovieActor.ActorID)>1;)
------- /sql ------------

The whole query (which works well) is:
-------- sql ------------
SELECT
tblActor.FullName
, tblMovieActor.Character
, tblMovie.Title
, tblMovie.IMDBc
FROM
tblMovie
RIGHT JOIN
(tblActor
RIGHT JOIN
tblMovieActor
ON
tblActor.ActorID = tblMovieActor.ActorID)
ON
tblMovie.MovieID = tblMovieActor.MovieID
WHERE
tblActor.ActorID In (
SELECT
tblMovieActor.ActorID
FROM
tblMovieActor
GROUP BY
tblMovieActor.ActorID
HAVING
Count(tblMovieActor.ActorID)>1;)
ORDER BY
tblActor.FullName;
-------------------

Here's Question 1.

Obviously (?), the WHERE Clause could be applied to either
(tblActor.ActorID in the Actor Table,) or (tblMovieActor.ActorID in the
JOIN Table.)

Which (if either) is "right", or conceptually better?

The question may be either complex or trivial. I'm still learning about
types of Join and how to use them. With luck I hope to learn enough to
answer my second question myself.

Question 2
How do I write a query that ...
My explanation is going to be bad. Sorry.

Assume Clint Eastwood is ActorID 42

The tblMovie has a Memo field IMDBInfo.
This contains information about the Movie, and mentions other Actors in the
Movie.
For example, MovieID 64 named "I made this Title Up." (linked to Rowan
Atkinson ActorID 32 as an Actor in MovieID 64) may have this text: "...
Shirley Temple, Clint Eastwood, Rowan Atkinson, Fred Dagg, ..."

Here's the question.
I want the query to select each Actor and (say) in the test for Clint
Eastwood, return the MovieID 64 because Clint Eastwood is in the IMDBInfo
field.

I think if I could ask the question properly I'd be a few steps closer to
the answer.
Any clues, anyone? Including, I suspect, Clue Number One.
I can get a result by creating a new table in code using nested RecordSets,
but I'd like to know at least if a Query is possible.

Thanks,
Alan

[Bob Quintal]

Alan Forsyth <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

I've been asked by a friend to help with his Movie database.
It's an informal home user record of his own movie watching
history. He's asked me to write a particular query, and I'm
stumped.

I have two questions.

[snip]

>
Here's Question 1.

Obviously (?), the WHERE Clause could be applied to either
(tblActor.ActorID in the Actor Table,) or
(tblMovieActor.ActorID in the JOIN Table.)

Which (if either) is "right", or conceptually better?

When querying on an outer join (left or right) one puts the
criteria in the predominant table, (that's the one that should
return all records). The reason for that is that when you query
on the limited table, (the one having matching records), unless
you specifically handle the null records, the query essentially
returns the same records as an inner join

so your FROM statement
RIGHT JOIN
(tblActor
RIGHT JOIN
tblMovieActor)
implies that you should use tblMovieActor.

However, if you used proper referential integrity, you should
not have any tblMovieActor rows with no corresponding actor or
movie, making the whole point of right join moot.

>
The question may be either complex or trivial. I'm still
learning about types of Join and how to use them. With luck I
hope to learn enough to answer my second question myself.

Question 2
How do I write a query that ...
My explanation is going to be bad. Sorry.

Assume Clint Eastwood is ActorID 42

The tblMovie has a Memo field IMDBInfo.
This contains information about the Movie, and mentions other
Actors in the Movie.
For example, MovieID 64 named "I made this Title Up." (linked
to Rowan Atkinson ActorID 32 as an Actor in MovieID 64) may
have this text: "... Shirley Temple, Clint Eastwood, Rowan
Atkinson, Fred Dagg, ..."

Here's the question.
I want the query to select each Actor and (say) in the test
for Clint Eastwood, return the MovieID 64 because Clint
Eastwood is in the IMDBInfo field.

I think if I could ask the question properly I'd be a few
steps closer to the answer.
Any clues, anyone? Including, I suspect, Clue Number One.
I can get a result by creating a new table in code using
nested RecordSets, but I'd like to know at least if a Query is
possible.

Thanks,
Alan

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Tom van Stiphout]

On Sat, 02 Jun 2007 23:33:50 -0500, Alan Forsyth <No*@iHome.nzwrote:

Re 1:
Better in the "primary" table, so tblActor. Putting criteria on an
outer join table can lead to unexpected results.

Re 2:
To test for occurrence of a substring in a string, you can use the
InStr function:
.... where InStr(IMDBInfo, FullName,vbTextCompare) 0

-Tom.

..

>I've been asked by a friend to help with his Movie database. It's an
informal home user record of his own movie watching history.
He's asked me to write a particular query, and I'm stumped.

I have two questions.

He has tblMovie and tblActor (and others.). They're Joined with
tblMovieActor, because one Movie can have many Actors and one actor can
have many Movies. He doesn't have a record for all Actors in each movie
but does just his favourites. Other Actors in the Movie will be in a block
of text in the IMDBInfo Memo field.

Here's part of his structure.

tblMovie
MovieID (PK)
Title Txt
IMDBInfo Memo
....More Fields.

tblActor
ActorID (PK)
FullName Txt (e.g. Clint Eastwood. In his database, NameFirst and
NameLast.)
... More Fields

The Join Table
tblMovieActor
MovieActorID (PK)
MovieID Long
ActorID Long
Character Text
------------------------

This works well for him with a Query such as this:
-------- sql ------------
SELECT
tblActor.FullName
, tblMovieActor.Character
, tblMovie.Title
, tblMovie.IMDBInfo
FROM
tblMovie
RIGHT JOIN
(tblActor
RIGHT JOIN
tblMovieActor
ON
tblActor.ActorID = tblMovieActor.ActorID)
ON
tblMovie.MovieID = tblMovieActor.MovieID;
------- /sql ------------

He can use this WHERE Clause:

---Part sql ------------
WHERE
tblActor.ActorID In (
SELECT tblMovieActor.ActorID
FROM tblMovieActor
GROUP BY tblMovieActor.ActorID
HAVING Count(tblMovieActor.ActorID)>1;)
------- /sql ------------

The whole query (which works well) is:
-------- sql ------------
SELECT
tblActor.FullName
, tblMovieActor.Character
, tblMovie.Title
, tblMovie.IMDBc
FROM
tblMovie
RIGHT JOIN
(tblActor
RIGHT JOIN
tblMovieActor
ON
tblActor.ActorID = tblMovieActor.ActorID)
ON
tblMovie.MovieID = tblMovieActor.MovieID
WHERE
tblActor.ActorID In (
SELECT
tblMovieActor.ActorID
FROM
tblMovieActor
GROUP BY
tblMovieActor.ActorID
HAVING
Count(tblMovieActor.ActorID)>1;)
ORDER BY
tblActor.FullName;
-------------------

Here's Question 1.

Obviously (?), the WHERE Clause could be applied to either
(tblActor.ActorID in the Actor Table,) or (tblMovieActor.ActorID in the
JOIN Table.)

Which (if either) is "right", or conceptually better?

The question may be either complex or trivial. I'm still learning about
types of Join and how to use them. With luck I hope to learn enough to
answer my second question myself.

Question 2
How do I write a query that ...
My explanation is going to be bad. Sorry.

Assume Clint Eastwood is ActorID 42

The tblMovie has a Memo field IMDBInfo.
This contains information about the Movie, and mentions other Actors in the
Movie.
For example, MovieID 64 named "I made this Title Up." (linked to Rowan
Atkinson ActorID 32 as an Actor in MovieID 64) may have this text: "...
Shirley Temple, Clint Eastwood, Rowan Atkinson, Fred Dagg, ..."

Here's the question.
I want the query to select each Actor and (say) in the test for Clint
Eastwood, return the MovieID 64 because Clint Eastwood is in the IMDBInfo
field.

I think if I could ask the question properly I'd be a few steps closer to
the answer.
Any clues, anyone? Including, I suspect, Clue Number One.
I can get a result by creating a new table in code using nested RecordSets,
but I'd like to know at least if a Query is possible.

Thanks,
Alan

[Alan Forsyth]

Tom van Stiphout <no*************@cox.netwrote in
news:rf********************************@4ax.com:

On Sat, 02 Jun 2007 23:33:50 -0500, Alan Forsyth <No*@iHome.nzwrote:

Re 1:
Better in the "primary" table, so tblActor. Putting criteria on an
outer join table can lead to unexpected results.

I understand that. I was referring to a case where the decision was
purely arbitrary, in the sense that either choice could never affect the
results.
I've decided that in that case my own arbitrary choice will be to use the
Table which created the ID.

Re 2:
To test for occurrence of a substring in a string, you can use the
InStr function:
... where InStr(IMDBInfo, FullName,vbTextCompare) 0

-Tom.

That's the nudge I needed, Tom. I was focused on using the Like Operator,
but of course Instr is just what I needed.

You know of course that if you use vbTextCompare then you must specify a
starting position. An obvious Typo above. :-)
You may not know that in Access 97 the Query Wizard insists that
vbTextCompare is a Text string and surrounds it with quotes, even if I
get in first with (vbTextCompare)
A quick function worked perfectly, though.
Function ActorIsHere(strIMDBInfo, strFullName)
ActorIsHere = InStr(1, strIMDBInfo, strFullName, vbTextCompare)
End Function
Using that, I get exactly what I want by using:

SELECT tblActor.ActorID,
tblMovie.MovieID,
ActorIsHere([tblMovie].[IMDBInfo],[tblActor].[FullName])
AS theChoice
FROM tblActor, tblMovie
WHERE
(((ActorIsHere([tblMovie].[IMDBInfo],[tblActor].[FullName]))>0));

Just what I needed.

Thanks,
Alan

<snip>

[Alan Forsyth]

Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:

[snip]

>>
Here's Question 1.

Obviously (?), the WHERE Clause could be applied to either
(tblActor.ActorID in the Actor Table,) or
(tblMovieActor.ActorID in the JOIN Table.)

Which (if either) is "right", or conceptually better?

When querying on an outer join (left or right) one puts the
criteria in the predominant table, (that's the one that should
return all records). The reason for that is that when you query
on the limited table, (the one having matching records), unless
you specifically handle the null records, the query essentially
returns the same records as an inner join

so your FROM statement
RIGHT JOIN
(tblActor
RIGHT JOIN
tblMovieActor)
implies that you should use tblMovieActor.

However, if you used proper referential integrity, you should
not have any tblMovieActor rows with no corresponding actor or
movie, making the whole point of right join moot.

And moot it is, which was the reason for my question. However, from your
information and Tom's, in situations where the choice is arbitrary my new
rule of thumb will be to choose the ID from the table where it was
created. If that choice stops making sense I'll think about it again.

In this particular situation I ended up with a Cartesian Join which
worked perfectly. I had my head stuck on criteria using "Like..."), and
Tom nudged me wiht InStr() which served me far better anyway!

Thanks,
Alan