Database join query

[Geoffo]

Hi,

I have a database I have created and was wondering if someone could help me with a select query (SQL is not my strong point!).

For this example, let's say my databse consists of four tables:
movie
person
involvement
movie_person_inv

movie consists of:
id
title
(ie: '1', 'A Few Good Men')

person consists of:
id
firstnames
surname
(ie: '1', 'Jack', 'Nicholson' / '2', 'Tom', 'Cruise' / '3', 'Rob', 'Reiner')

involvement consists of:
id
type
(ie: '1', 'Actor' / '2', 'Director' / '3', 'Star')

movie_person_involvement consists of:
movie_id
person_id
inv_id
(ie: '1', '1', '1' / '1', '2', '3' / '1', '3', '2')

I am trying to select the name of the Director of 'A Few Good Men', but I'm not very good at SQL statements and my results are not what I want. This is what I'm trying:

SELECT CONCAT(firstnames, ' ', surname) AS full_name
FROM movie, person, movie_person_inv
WHERE movie.id = 1
AND movie_person_inv.movie_id = movie.id
AND movie_person_inv.inv_id = 2

So full_name should = 'Rob Reiner'.

But this returns everyone associated with the film and not just the Director. Could someone please tell me where I am going wrong, and if possible correct my select statement with some form of explanation, as I can't see why this would return 'everyone'.

Any help you could give would be very much appreciated.

Thanks,

Geoffo

[sid]

SELECT CONCAT(p.firstnames, ' ', p.surname) AS full_name
FROM movie m, person p, movie_person_inv pmi
WHERE pmi.movie_id = m.id
AND pmi.inv_id = 2
AND pmi.person_id = p.id
AND m.title LIKE "A Few Good Men";

[Geoffo]

Thanks sid, that works perfectly.

Do the 'p', 'm' and 'pmi' act as aliases of the tables?

So 'longhand' it would be:

SELECT CONCAT(person.firstnames, ' ', person.surname) AS full_name
FROM movie, person, movie_person_inv
WHERE movie_person_inv.movie_id = movie.id
AND movie_person_inv.inv_id = 2
AND movie_person_inv.person_id = person.id
AND movie.title LIKE "A Few Good Men";

Well, that seems to work as well, so I assume that the answer to my question above is 'yes'.

This is not something I have come across before. Thank you.

[sid]

indeed - the answer is YES ;-)

i just find it a lot easier to work with those aliases - as you noticed correctly.

im glad i could help.
keep it rocking!