时间戳

首页 > 解决方案 > subquery must return only one column , how can i fix it

sql - subquery must return only one column , how can i fix it

问题描述

How many times has any customer whose first name starts with “A” rented movies in which “JULIA MCQUEEN” (first name Julia) acted? Your solution must use joins and your output should be a single number.

SELECT count(r.rental_id) 
FROM rental r
JOIN customer c on r.customer_id = c.customer_id
JOIN store s on c.store_id = s.store_id
JOIN inventory i on s.store_id = i.store_id
JOIN film_actor fa on i.film_id = fa.film_id
JOIN actor a on fa.actor_id = a.actor_id

WHERE  a.actor_id = 21 and c.first_name = (select * FROM customer where first_name like 'A%');

how can i fix it

标签： sql

解决方案

  1. 而不是c.first_name = (select * ...) use c.first_name = like 'A%'`
  2. 也用GROUP BY c.customer_id。
  3. 根据问题，您应该具有演员名称为的条件a.first_name = 'Julia'。

您的完整查询将如下所示。

SELECT c.customer_id, count(r.rental_id) rented_count
FROM rental r
JOIN customer c on r.customer_id = c.customer_id
JOIN store s on c.store_id = s.store_id
JOIN inventory i on s.store_id = i.store_id
JOIN film_actor fa on i.film_id = fa.film_id
JOIN actor a on fa.actor_id = a.actor_id
WHERE  a.first_name = 'Julia' and c.first_name like 'A%'
GROUP BY c.customer_id

推荐阅读