Trying to take a list of the name, surname and number of movies from each director in the same query-CodePudding

I have two tables, one with the directors id, name and surname. And another with the movies id, title and directors id. Im trying to make a query in sql, where it shows the name, surname and number of movies that has each director, thanks :).

CodePudding user response：

Imagining this as your tables and their column names in MySQL database

-- create
CREATE TABLE DIRECTOR (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  surname TEXT NOT NULL
);

CREATE TABLE MOVIES (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  director_id INTEGER,
  FOREIGN KEY (director_id) references DIRECTOR(id)
);

The following will be the query to fetch the name, surname and number of movies that has each director made

SELECT name, surname, count(*)
FROM DIRECTOR d, MOVIES m
WHERE d.id = m.director_id
GROUP BY name, surname;

CodePudding user response：

You will have to specify the db name at the beggining like:

SELECT * FROM DB_1.dbo.t1
UNION ALL
SELECT * FROM DB_2.dbo.t2
UNION ALL
SELECT * FROM DB_3.dbo.t3