CODEWITHSUNDEEP
sqloracle-databasejoinmultiple-select
falkon114
falkon114

Reputation: 11

SQL table joins and subqueries

I need to join 3 tables together, and get dates, prices, customer ids from them. Here's the description:

First you need to figure out the customer id’s of anyone who purchased a product with the division of ‘Bike Accessories’ from Jan 1, 2012 till now. To get this you will need to join orders to orderlines to products.

Then in your parent query, you will join orders to orderlines and filter on customer id’s that are in your subquery results. To calculate lifetime order revenue you’ll need to do an aggregate function on the result of price * quantity.

So I have a customers table with customer id, an orderlines table with order number, price, and quantity, an orders table with customer id, order number, and order date, and a products table with division (needed to get 'Bike Accessories' retrieved). I've written this, and I'm getting errors from "invalid identifier" to "missing expressions" depending on what I move around.

select bc_orders.order_number, bc_orderlines.price, bc_orderlines.quantity, bc_orderlines.quantity*bc_orderlines.price AS "Total Revenue"
  from (select bc_orders.*, bc_orderlines.*, bc_products.* 
  from bc_customers
   join bc_orders
    on bc_orders.order_number = bc_orderlines.order_number
   join bc_products
    on bc_products.sku = bc_orderlines.sku

where bc_orders.order_date >= '01-JAN-2012')

inner join bc_orderlines 
  on bc_orders.order_number = bc_orderlines.order_number

And I get back:

Error at Command Line:5 Column:31
Error report:
SQL Error: ORA-00904: "BC_ORDERLINES"."ORDER_NUMBER": invalid identifier

Help!

Upvotes: 0

Views: 128

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The from clause is evaluated the same way that you read it. That means that before a table alias can be mentioned in the on clause, it needs to be defined as a table. You are using orderlines before it is defined. Hence the error.

This is easily fixed:

from (select bc_orders.*, bc_orderlines.*, bc_products.* from bc_customers join bc_orders on bc_orders.customer_number = bc_customers.customer_number join bc_orderlines on bc_orders.order_number = bc_orderlines.order_number join bc_products on bc_products.sku = bc_orderlines.sku where bc_orders.order_date >= '01-JAN-2012' ) bc

Some notes:

  • I haven't fixed all the problems in the query, just the one you asked about.
  • You don't need the subquery.
  • Your query would be much more readable with table aliases. I encourage you to use them.
  • The subquery, as written, will have ambiguous columns.

Upvotes: 0

Related Questions