SQL How to select original(distinct) values from table without using distinct, group by and over key-CodePudding

Currently I'm studying and I received task to write query (join 4 tables: people, goods, orders and order details). So main table Order_details has two columns: Order_id and Good_id, in order to make possible to have many goods in one order (e.g. order with id = 1 has 3 rows in Order_details table but has different goods primary keys in each row). So the problem is that I don't know any other possible methods(besides using group by, distinct or over()) to receive only one row of each order in Order_details table (like I would get by using for example Distinct keyword). I'm receiving completely same rows of each order (with same Order_id and Good_id) but i don't know how to get only one row of each order. Here's my query(so basically i need to select sum of all goods in order but i don't think that it really matters in my problem) and scheme (if it'll help) By the way I'm working with MYSQL.

SELECT 
  Order_details.Order_id, 
  Orders.Date, People.First_name, 
  People.Surname, 
  (
    SELECT SUM(Goods.Price * Order_details.Quantity) 
    FROM Order_details, Goods  
    WHERE Order_details.Good_id = Goods.Good_id 
    AND Order_details.Order_id = Orders.Order_id
  ) AS Total_price
FROM Order_details, Goods, Orders, People 

WHERE Order_details.Order_id = Orders.Order_id 
  AND Order_details.Good_id = Goods.Good_id 
  AND Order_details.Order_id = Orders.Order_id 
  AND Orders.Person_id = People.Person_id
ORDER BY Order_id ASC;

I have tried several methods, but still cant figure it out. Maybe somehow it is possible with subquery? But i'm not sure... (I have tried method with UNION but it's not the key as well)

CodePudding user response：

Remove the Goods and Order_details tables from the FROM clause and the corresponding conditions in the WHERE clause. You are not selecting anything from it anyway, except the SUM in the subselect. The Order_id can be selected from the Orders table. The join is just causing multiple rows per order.

Also please don't join with comma. Use the JOIN .. ON syntax. This makes it easier to see if the join conditions are reasonable.

SELECT 
  Orders.Order_id 
  Orders.Date,
  People.First_name, 
  People.Surname, 
  (
    SELECT SUM(Goods.Price * Order_details.Quantity) 
    FROM Order_details
    JOIN Goods ON Order_details.Good_id = Goods.Good_id
    WHERE Order_details.Order_id = Orders.Order_id
  ) AS Total_price
FROM Orders
JOIN People ON Orders.Person_id = People.Person_id
ORDER BY Orders.Order_id ASC;

CodePudding user response：

you can use row_number() for this kind of thing it will assign a row number based on your criteria and then you can just pick the rows where the value is 1.

with t as (SELECT 
  Order_details.Order_id, 
  Orders.Date, People.First_name, 
  People.Surname, 
   row_number() over (
              partition by order_id, good_id
              order by order_id, good_id) rn,
  (
    SELECT SUM(Goods.Price * Order_details.Quantity) 
    FROM Order_details, Goods  
    WHERE Order_details.Good_id = Goods.Good_id 
    AND Order_details.Order_id = Orders.Order_id
  ) AS Total_price
FROM Order_details, Goods, Orders, People 

WHERE Order_details.Order_id = Orders.Order_id 
  AND Order_details.Good_id = Goods.Good_id 
  AND Order_details.Order_id = Orders.Order_id 
  AND Orders.Person_id = People.Person_id
ORDER BY Order_id ASC)
 select * from t where rn = 1