CODEWITHSUNDEEP
mysqlsql
maximus4996
maximus4996

Reputation: 13

Ad Campaign on Sakila Database

Can Someone help me with the SQL code for:

Suppose you are running an advertising campaign in Canada for which you need the film_ids and titles of all the films released in Canada. List the films in the alphabetical order of their titles.

I tried something but I am not able to figure it out.

SELECT film_id, title 
FROM film

WHERE COUNTRY IN (SELECT COUNTRY FROM COUNTRY
INNER JOIN FILM USING (FILM_ID)
INNER JOIN INVENTORY USING (STORE_ID)
INNER JOIN STORE USING (ADDRESS_ID)
INNER JOIN ADDRESS USING (CITY_ID)
INNER JOIN CITY USING (COUNTRY_ID)
WHERE COUNTRY = ('Canada'))

ORDER BY Title;

I am getting this error.

ERROR 1054 (42S22) at line 3: Unknown column 'FILM_ID' in 'from clause'

Note: Data for only the required tables has been added for every question. I have to determine which tables are to be used for solving this question.

Sample expected output

Sakila Database

Upvotes: 0

Views: 2745

Answers (3)

Sonu
Sonu

Reputation: 11

select Film_id, Title
from film
inner join inventory
using (film_id)
inner join store
using (store_id)
inner join address
using (address_id)
inner join city
using (city_id)
inner join country
using (country_id)
where country = 'Canada'
group by film_id, title
order by title;

Upvotes: 0

blackhole
blackhole

Reputation: 204

select Film_id, Title
    from film
inner join inventory
    using (film_id)
inner join store
    using (store_id)
inner join address
    using (address_id)
inner join city
    using (city_id)
inner join country
    using (country_id)
where country = 'Canada'
group by film_id, title
order by title;

Upvotes: 0

matigo
matigo

Reputation: 1461

The issue comes from here:

SELECT COUNTRY FROM COUNTRY
 INNER JOIN FILM USING (FILM_ID)

Country does not have a film_id column, so it does not work. That said, you can simplify your query a little bit to get the results you need:

SELECT f.film_id, f.title 
  FROM Film f INNER JOIN Inventory inv ON f.film_id = inv.film_id
              INNER JOIN Store sto ON inv.store_id = sto.store_id
              INNER JOIN Address addr ON sto.address_id = addr.address_id
              INNER JOIN City c ON addr.city_id = c.city_id 
              INNER JOIN Country co ON c.country_id = co.country_id 
 WHERE co.country = 'Canada'
 ORDER BY f.title, f.film_id;

The subquery is unnecessary and references a column that does not exist in the Film table.

Upvotes: 2

Related Questions