List the all the different actors by using sqlite and actresses who have starred in a work with Nicole Kidman (born in 1967). Details: Print only the names of the actors and actresses in alphabetical order. The answer should include Nicole Kidman herself. Each name should only appear once in the output. Note: As mentioned in the schema, when considering the role of an individual on the crew, refer to the field category. The roles "actor" and "actress" are different and should be accounted for as such.

I have three classes

1. crew:

CREATE TABLE crew (
  title_id VARCHAR, -- REFERENCES titles (title_id),
  person_id VARCHAR, -- REFERENCES people (person_id),
  category VARCHAR,
  job VARCHAR,
  characters VARCHAR
);
CREATE INDEX ix_crew_title_id ON crew (title_id);
CREATE INDEX ix_crew_person_id ON crew (person_id);

2. people:

CREATE TABLE people (
  person_id VARCHAR PRIMARY KEY,
  name VARCHAR,
  born INTEGER,
  died INTEGER
);
CREATE INDEX ix_people_name ON people (name);

3. titles:

CREATE TABLE titles (
  title_id VARCHAR PRIMARY KEY,
  type VARCHAR,
  primary_title VARCHAR,
  original_title VARCHAR,
  is_adult INTEGER,
  premiered INTEGER,
  ended INTEGER,
  runtime_minutes INTEGER,
  genres VARCHAR
);
CREATE INDEX ix_titles_type ON titles (type);
CREATE INDEX ix_titles_primary_title ON titles (primary_title);
CREATE INDEX ix_titles_original_title ON titles (original_title);

My solution is:

SELECT DISTINCT name FROM people
GROUP BY
    person_id
HAVING person_id IN (SELECT person_id FROM crew WHERE title_id IN (SELECT title_id FROM crew WHERE person_id IN (SELECT person_id FROM people WHERE name='Nicole Kidman')))
AND category IN ('actor', 'actress')
ORDER BY name ASC;

But the output shows an error: in prepare, no such column: category (1)

If I delete this line: AND category IN ('actor', 'actress') it works

So I want to know how can I add a filter to check category should be 'actor' or 'actress' Hope someone could give me a hint

CodePudding user response：

SELECT DISTINCT name FROM people
GROUP BY
    person_id
HAVING person_id IN (SELECT person_id FROM crew WHERE title_id IN (SELECT title_id FROM crew WHERE person_id IN (SELECT person_id FROM people WHERE name='Nicole Kidman')) AND category IN ('actor', 'actress'))
ORDER BY name ASC;

This works because I put person_id IN (SELECT person_id FROM people WHERE name='Nicole Kidman')) and category IN ('actor', 'actress') into the same WHERE' clause. Otherwise, it can not find categoryin the table because it does not included inWHERE`