0

Hello below is my Code

DB: https://dev.mysql.com/doc/sakila/en/sakila-structure.html

select concat(first_name,' ',last_name) from
customer where customer_id in (
select customer_id from (
select customer_id, count(rental_id) as num
from 
category 
inner join film_category using(category_id) 
inner join film using(film_id) 
inner join inventory using(film_id) 
inner join rental using (inventory_id)
where name='Sci-Fi'
group by customer_id, rental_id)
where num > 5)T)

when i am executing i am getting the below error

ERROR 1248 (42000) at line 2: Every derived table must have its own alias

Expected Outcome is "full names of customers who have rented sci-fi movies more than 5 times. Arrange these names in the alphabetical order"

Could you please let me know what is the mistake i am doing?

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Also, links die. Insert images/links using edit functions. Make your post self-contained. – philipxy Jun 13 '20 at 23:03
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Jun 13 '20 at 23:19

3 Answers3

1

Welcome to SO!

First, it seems like you have 3 opening ( parens and 4 closing ) parens. You should delete the last parenthesis so you have balanced parens.

After that, you want to apply the alias to the deepest level query. (Similar question: What is the error “Every derived table must have its own alias” in MySQL?) You have...

where name='Sci-Fi'
group by customer_id, rental_id)
where num > 5)T)

You probably want...

where name='Sci-Fi'
group by customer_id, rental_id) AS T
where num > 5)

(Don't forget, there is no need for that extra closing paren, so you can see I removed it. It might be part of a bigger query you have, but it doesn't help the standalone code in the question.)

This will stop the immediate error that you're seeing. At least, now on my database, the error I see is: ERROR 1146 (42S02): Table 'db.customer' doesn't exist.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
0
select concat(first_name, ' ', last_name) as Customer_name
from category
inner join film_category
using (category_id)
inner join film
using (film_id)
inner join inventory
using (film_id)
inner join rental
using (inventory_id)
inner join customer
using (customer_id)
where name = 'Sci-Fi'
group by Customer_name
having count(rental_id) > 3
order by Customer_name;
Pang
  • 9,564
  • 146
  • 81
  • 122
chra
  • 1
  • Thanks for your answer but it only consists of code so could you please update it and provide some explanation alongside with your code? – shaedrich May 10 '21 at 10:27
0
select concat(first_name,' ',last_name) as customer_name from customer
inner join rental 
using(customer_id)
inner join inventory 
using(inventory_id)
inner join film 
using(film_id)
inner join film_category
using(film_id)
inner join category
using(category_id)
where name in ('sci-fi') 
group by customer_name
having count(rental_id) > 2
order by customer_name
shaedrich
  • 5,457
  • 3
  • 26
  • 42
Princi Verma
  • 41
  • 1
  • 1
    Thanks for your answer but it only consists of code so could you please update it and provide some explanation alongside with your code? – shaedrich May 10 '21 at 10:27