Board index » delphi » Need help with SQL statement

Need help with SQL statement

I would try the following statement:

SELECT F.Filmnr, F.Tapenr, F.Name FROM Film F, Actor-Film2 AF, Actor A
 WHERE A.Actor LIKE '%Bruce%' AND AF.Actornr = A.Actornr AND F.Filmnr = AF.Filmnr

Best wishes
Alexander

Quote
> Jimmy Rasmussen wrote:

> I've been cracking my brain with this database puzzle and I can't seem to get it right. Perhaps some
> of you have some ideas.

> I've got three tables: One with actors, one with movies and one that shows which actors starrs in the
> movies.

> I wanna do this: Get the names of movies that are starring a specific actor. (This actor's name could
> be represented by a textbox like in the picture)

> I've tried using many different SQL statements. Here's an example:

> SELECT * FROM Film INNER JOIN "Actor" ON "Actor-Film2".Actornr = Actor.Actornr WHERE Film."Film nr" =
> "Actor-Film2".Filmnr;

> Do any of you know how to make an SQL statement that will do what is required?

 

Re:Need help with SQL statement


You may try the next one, May it is not the faster one, but it is clear to
understand
------------
I read the following one, like
Quote
> SELECT F.Filmnr, F.Tapenr, F.Name FROM Film F, Actor-Film2 AF, Actor A
>  WHERE A.Actor LIKE '%Bruce%' AND AF.Actornr = A.Actornr AND F.Filmnr =

AF.Filmnr

Hey database server, for all possible mixte row beteewn the 3 tables, and in
this mexiture select only
the condition i gave you (i am not sur, its my understanding)
-----------
I read the following one, like

SELECT F.Filmnr, F.Tapenr, F.Name FROM Actor-Film2 AF
  LEFT JOIN Film F ON F.Filmnr = AF.Filmnr
  LEFT JOIN Actor A ON A.Actornr  = AF.Actornr
  WHERE      A.Actor LIKE '%Bruce%'

Hey database server for all row present in AF add the corresponding row from
the F and A table
Then select the ones where Actor is like '%bruce%'
---------

"Alexander Nitschke" <alexander.nitsc...@inzpla.de> a crit dans le message
news: 3ADEE116.5483C__BEGIN_MASK_n#9g02mG7!__...__END_MASK_i?a63jfAD$z__@inzpla.de...

Quote
> I would try the following statement:

> SELECT F.Filmnr, F.Tapenr, F.Name FROM Film F, Actor-Film2 AF, Actor A
>  WHERE A.Actor LIKE '%Bruce%' AND AF.Actornr = A.Actornr AND F.Filmnr =
AF.Filmnr

> Best wishes
> Alexander

> > Jimmy Rasmussen wrote:

> > I've been cracking my brain with this database puzzle and I can't seem

to get it right. Perhaps some
Quote
> > of you have some ideas.

> > I've got three tables: One with actors, one with movies and one that

shows which actors starrs in the
Quote
> > movies.

> > I wanna do this: Get the names of movies that are starring a specific

actor. (This actor's name could
Quote
> > be represented by a textbox like in the picture)

> > I've tried using many different SQL statements. Here's an example:

> > SELECT * FROM Film INNER JOIN "Actor" ON "Actor-Film2".Actornr =

Actor.Actornr WHERE Film."Film nr" =
Quote
> > "Actor-Film2".Filmnr;

> > Do any of you know how to make an SQL statement that will do what is

required?

Other Threads