0

I need to create a SELECT statement, this SELECT statement will use an IN to check whether a department has had a sale with a price over 90.00 dollars BUT the sql MUST use the WITH statement which will be used to select all columns from sales where the price is greater than 90.00, you must call this sub-query special_sales.

departments table schema
id
name

sales table schema
id
department_id (department foreign key)
name
price
card_name
card_number
transaction_date

resultant table schema
id
name

NOTE: Your solution should use pure SQL.

IN WHERE WITH special_sales
SELECT  DISTINCT d.* 
FROM    departments d 
JOIN    sales       s 
WHERE   d.id = s.department_id 
AND     s.price > 90 
ORDER BY d.id;

this is the code I did but I received an sql error

Siyual
  • 16,415
  • 8
  • 44
  • 58
anonymous
  • 1
  • 2
  • MySQL doesn't support CTE. You'll need to use a different DBMS. – Barmar Jan 18 '17 at 16:59
  • Mysql doesn't support WITH clause until version 8. http://stackoverflow.com/questions/324935/mysql-with-clause And you should at least try to read some documentation first before came here and ask us to solve your assignment. – Jorge Campos Jan 18 '17 at 16:59
  • 2
    This sounds suspiciously like a homework assignment or exercise. If so, you should clarify that in your question and reassure us that you're not using us to cheat on an assignment. You also need to provide us details of *what* error you received, and why it wasn't clear how to resolve it. – IMSoP Jan 18 '17 at 17:00
  • this is not an assignment it is a challenge on code wars and this is the error PG::SyntaxError: ERROR: syntax error at or near "SELECT" LINE 2: SELECT DISTINCT id,name FROM departments d JOIN sales s WHER... – anonymous Jan 18 '17 at 17:06

0 Answers0