Re: SQL Ignorance

Alan Forsyth <Not@xxxxxxxx> wrote in
news:Xns9944A879D279ANotiHomenz@xxxxxxxxxxxxxx:

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

.

Relevant Pages

  • Re: Creatinig a database to update in alphabetical order
    ... You can then create a Query which takes all of the records in the ... Some attributes of a movie would be the Title, the studio, ... MovieID Autonumber Primary Key ' links tables together ... Keyword ...
    (microsoft.public.access.gettingstarted)
  • Re: Creatinig a database to update in alphabetical order
    ... John M. it is like you are reading my mind, ... know I am a movie fanatic. ... > should not be routine) displayed as a query datasheet. ... > Keyword ...
    (microsoft.public.access.gettingstarted)
  • Re: Queries and OO
    ... You caught ognl with its pants down, since I don't believe it supports ... the ognl query is going to look something like: ... each of them and answer the movie with the min result. ... perform the birthDate operation and answer the actor with the max result. ...
    (comp.object)
  • Re: Undocumented Filter?
    ... I think Tom was referring to the same movie I was, "Army of Darkness," ... I currently re-mask the SQL of the source query by taking a query I've set ... That solution gives me a Filter property for the query (since I'm working ... > That was not Bruce Campbell, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Queries and OO
    ... > code with typos in unfamiliar syntax is too much of a burden. ... query is supposed to do. ... table vs. Movie implemented as an object? ...
    (comp.object)