0

I have 3 tables:

  1. film(film_id, filmname) PK (film_id)
  2. category(category_id, categoryname) PK (category_id)
  3. film_category(contains both primary keys (film_id) and (category_id))

The problem is that I want to select all filmname from film tables where category_id = 3.

How to do this mysql select?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
rajesh
  • 323
  • 1
  • 2
  • 12

2 Answers2

4
select
  film.filmname
from
  film
  join film_category
  on film.film_id = film_category.film_id
where
  film_category.category_id = 3

Further info.

Yaakov Shoham
  • 10,182
  • 7
  • 37
  • 45
0
SELECT filmname
FROM film NATURAL JOIN film_category
WHERE category_id = 3
Amadan
  • 191,408
  • 23
  • 240
  • 301
  • 1
    `NATURAL JOIN` is the worst thing in ANSI-92 join syntax. – OMG Ponies Oct 21 '11 at 03:47
  • I normally agree. However, I'd never have `film_id` in `film`. Given the setup of the database, this is the most NATURAL way to write it. (sorry about the pun.) – Amadan Oct 21 '11 at 03:48
  • @OMGPonies: Why? If table1 and table2 as natural connection (and only one, and it's defined in the DDL), why not using it for simplifing qureies? – Yaakov Shoham Oct 21 '11 at 03:52
  • 1
    @Y.Shoham: [This](http://stackoverflow.com/questions/6039719/is-natural-join-considered-harmful-in-production-environment/6039758#6039758) is why. – Amadan Oct 21 '11 at 03:54
  • @@Y.Shoham: Amadan beat me to it – OMG Ponies Oct 21 '11 at 03:55
  • @OMGPonies: I didn't mean that - I mean it's not DRY. I'd have `films (id, name)`, `film_categories (film_id, film_category_id)`, `film_categories (id, name)`. Only foreign keys deserve the table prefix. – Amadan Oct 21 '11 at 03:56
  • Amadan: `id` column fails Spolsky's hallway test. And it's not consistent if one column is `id` while the rest have a prefix that makes things very obvious. – OMG Ponies Oct 21 '11 at 04:12
  • @OMGPonies: How is `film_id` better than `films.id`? "The rest" don't have the prefix, only foreign keys do. `film_name` or `filmname` is equally noisy. In my view, if it has a prefix, it's a foreign key - look for its real value in the other table (and it's a natural read from `films.category_id`). `films.id` also reads more naturally than `films.film_id` (which you still have to do to distinguish from `film_categories.film_id`). Same reason why Ruby class `String` does not have method called `.string_length` - you know you're applying it to Strings. – Amadan Oct 21 '11 at 04:24
  • Spolsky hallway test reveals: "id means *what*?" id of what exactly, because there can be more than one id column in a table -- so, one gets to be id when the later was could be the more logical use? There's nothing to gain using `film.id` when `film.film_id` solves problems. – OMG Ponies Oct 21 '11 at 04:27
  • 1
    @OMGPonies: And even in foreign keys, prefix is not necessarily a table name. To get a nonsimplistic example, in `people (id, first_name, last_name, father_id, mother_id, city_id)`, `cities (id, name, mayor_id)` - it is still clear what everything is. `cities.name` is the name of the city. `people.mother_id` will let you find a mother by her `id`. – Amadan Oct 21 '11 at 04:30
  • @OMGPonies: If I have an ID that is not autoincrement, I will call it by its semantic value. I will only make `id` an autoincrement, semantics-free field, in absence of a better unique key. Thus, the question of "`id` means what" is exactly that - nothing. If I have social security numbers available, or whatever unique ID is intrinsic to a class, `people (ssn)` is what goes into the table. If you have to prefix `id`, then you need to prefix `name` too, and `address`... everything (as there's no logical cutoff where your hallway test stops working). If everything's prefixed, it's meaningless. – Amadan Oct 21 '11 at 04:35
  • @OMGPonies: It's an enlightening discussion, to be sure - seeing how others perceive readability, for instance - but I'm pretty sure you won't change my mind about this. – Amadan Oct 21 '11 at 04:36
  • 1
    Who said the prefix had to be a table name? Likewise for your naming convention and how you interpret what I said. When using table aliases, you'd never see `CITIES.name` - more like `c.name`. Again - name of what? First, last or middle? I understand you're entrenched in your belief, but you might want to stop ignoring pertinent details like 3rd party, respected, design tests besides other facts. – OMG Ponies Oct 21 '11 at 14:01
  • @OMGPonies: I said the prefix would be a table name, and then I was clarifying what I meant (and it split into a next comment due to char limit). The difference between `c.name` and `cities.name` is no longer a discussion of database design but of coding style, and `cities.name` is clearly the name of the city. I do not feel I am ignoring anything: I like the hallway test, just questioon its applicability to this issue. Rather that I am engaged into an interesting and respectful discussion of difference of opinion, explaining my stance and asking pointed questions in order to understand yours. – Amadan Oct 22 '11 at 01:36
  • @OMGPonies: Respectful, that is, until now - since I do not think I deserved a patronizing comment like your last sentence. Which shows me how much *you* think of *my* arguments. Thank you, I'll let myself out. – Amadan Oct 22 '11 at 01:38
  • @Amadan: I addressed points you raised, and you shouldn't ignore my example of why the column being simply "id" isn't a wise decision. – OMG Ponies Oct 22 '11 at 02:48