Working on my final project for school in Oracle. I have my databases built, just need to add queries.

I have a table with services and prices and I have a table with customer orders and services. What I want to do - If a customer has purchased more then 5 dogwalks, they get a discount on dogwalks. (Instead of $16 a walk, it becomes $14)

select customer_name, order_info.owner_num, service_num
from order_info, owner_info
where 'S01' > '5';

When I query this, I get every service number... (S01, S02, S03)...

Any ideas on what I am doing wrong?

CodePudding user response：

You can:

• Use IN rather than OR
• Specify a JOIN condition for the tables (do not use legacy comma joins)
• JOIN and GROUP BY the primary key of the owner, which should not be their name otherwise you risk giving a discount to two people both named "Jane Smith" when the each have only had 3 orders
• Compare the COUNT() in the HAVING clause to a number and not a string.

Like this:

SELECT MAX(customer_name),
       service_num,
       COUNT() count
FROM   owner_info 
       INNER JOIN order_info 
       ON (owner_info.customer_id = order_info.customer_id)
WHERE  service_num IN ('S01', 'S04')
GROUP BY
       owner_info.Customer_id,
       order_info.Service_num
HAVING COUNT() > 5;

CodePudding user response：

Imaging that u have 2 table. The first table about service(include service, price) and the second table about customer(include customer, service). And if you want to get the customers who have ordered service 'S01' or 'S04' u can try this query:

select 
   customer, service, count(service) as count_of_service
from 
   cust_table
where
  service = 'S01' or service = 'S04'
groupby 
   customer, service
having 
   count_of_service > 5