List:General Discussion« Previous MessageNext Message »
From:Marc Antony Vose Date:November 10 2000 9:53am
Subject:select distinct
View as plain text  
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
Thread
adding a digit in a columnMy Esprit10 Nov
  • Re: adding a digit in a columnRolf Hopkins10 Nov
  • select distinctMarc Antony Vose10 Nov
    • RE: select distinctDon Read10 Nov
  • RE: adding a digit in a columnDon Read10 Nov
  • Re: adding a digit in a columnInformática - Veríssimo Rio10 Nov
    • Re: adding a digit in a columnBasil Hussain10 Nov