normalization - too many records returned 1

[jgroove]

I have been working on normalizing a bunch of table that I have. The problem is that now that I have all the data in a bunch of different tables, when I join them on a select I am not getting exactly what I want.

example
the problem is I took all the actors out of the movie table and moved them to an actor table and created a table inbetween (is there a special name for this table?) them to join the movie table and actor table together sort of like this

before

movie
--------
movieid
title
actors (comma deliminated list)


after

movie
-------
movieid
title

actor
-------
actorid
firstname
lastname

actor_in_movie
--------------
actorid
movieid


so now when i do a select on movies and join them to the actors i get many records back for one movie, one record for each actor. (i completely understand why this is happening, now at least So here is my question, is there a way to return just one movie record, with one column that has all the actors in it?

jgroove

 

[angiole]

The table in between was called a bridge table, at least that was 'mot de riguer" at the time.

Your query is returning exactly what you asked for, and that's the price of normalization, when is spread out to unique key tables.

Concept works great for transactional databases, but falls a little short on the reporting side.

I would gues yu would probably be presenting the report in a third party tool anyway which would take care of moving the data around at the presentation level.

Cheers AA 8~)

 

[jgroove]

thank you for your response. the benifits that we are reaping from normalization far outwieghs this problem. i was just checking to see if i could fix this with sql before i moved it to the logic.

jgroove

 

[angiole]

Of course, a single SQL statement has difficulty here, but a procedure would be able to be build your actors list for each movie, and present the following:

ID   MOVIE            ACTORS

12   Vanishing Point  Barry Newman,Charlotte Rampling, ...

Costs a little CPU/IO wise, nut it works pretty well enough.


AA 8~)