Hi guys, I've been trying this for hours but keep getting the same error. I'm trying to join the tables, but eliminate repeated data, hence the group by function.
The SQL is as follows:
select d.directorno, name
from film f, director d
where f.directorno=d.directorno and genre !='SCIENCE FICTION'
group by d.directorno
Without the group by function, it gives me repeated entries, does anyone have any idea on how to fix this?
You need an aggregate operator on the "name" column. Or group by both directorno and name
Thanks ! I have one other question, right now I have:
Customer: custno(PK), name, address, DVDs, registration
Rental: dvdno(FK), custno(FK), hiredate, returndate
DVD: dvdno(PK), filmno, dateofpurchase
Film: filmno(PK), title, year, genre, director(FK), star(FK)
Star: Starno(PK), name, birthplace, birthyear, deathyear
Director: directorno(PK), name, birthyear, deathyear
However, I have been tasked with having more than one star per film. Right now my database only allows one star per film. Do you have any idea how I would amend this? Would it require an extra table?
Looks like it. You could have a FilmStarLink table with columns [filmno], [Starno] and appropriate FKeys (and move data from [star] to the FilmStarLink table, and then drop the [star] column in [Film] table.
