Hi there:
I've got two tables, one of films, and one of film distributors. In
the films column, there is a possible entry of one of the distributor
unique IDs in each of three columns: fil_dist_gen, fil_dist_edu and
fil_dist_broad. There is also a timestamp column called
"fil_updated, which represents the las time the record was modified.
What I'm trying to select is each of the distributor names that are
reflected in the films table somewhere, and the amount of days since
the last update of that entry.
So, here's the select statement i'm using:
SELECT
DISTINCT distributors.dis_id,
distributors.dis_name,
(TO_DAYS(NOW())-TO_DAYS(films.fil_updated)) as days_updated
FROM
films, distributors
WHERE
(films.fil_dist_gen=distributors.dis_id ||
films.fil_dist_edu=distributors.dis_id ||
films.fil_dist_broad=distributors.dis_id)
ORDER BY
distributors.dis_name
The problem is that it is returning some of the distributor names
more than once. The culprit seems to be if the days_updated differs.
Here's a current result from this query:
+--------+---------------------------------------+--------------+
| dis_id | dis_name | days_updated |
+--------+---------------------------------------+--------------+
| 12 | American Friends Service Committee | 2 |
| 16 | California Newsreel | 2 |
| 10 | Cinema Guild | 2 |
| 5 | CS Associates | 2 |
| 14 | Educational Video Center | 2 |
| 15 | Fanlight Productions | 2 |
| 4 | Filmakers Library | 2 |
| 8 | Films for the Humanities | 2 |
| 17 | Global Action Project, Inc. | 2 |
| 6 | Green Mountain Post Films | 2 |
| 1 | Independent Television Service (ITVS) | 2 |
| 1 | Independent Television Service (ITVS) | 1 |
| 9 | Lava Video | 2 |
| 18 | Media Guild, The | 2 |
| 3 | New Day Films | 2 |
| 3 | New Day Films | 1 |
| 13 | One World Communication | 2 |
| 11 | Seventh Art | 2 |
| 7 | Skyline Community | 2 |
| 20 | Third World Newsreel/Camera News | 2 |
| 2 | Women Make Movies | 2 |
+--------+---------------------------------------+--------------+
As you can see, ITVS and New Day Films are coming back twice because
(apparently) they have different days_updated.
How can I get this to return each distributor name just once?
Cheers,
--
Marc Antony Vose
http://www.suzerain.com/
I'm looking for something in an after dinner burrito.
-- Homer Simpson
