There is a Orders table and an OrderItem table that lists items for each individual order. I need to find only those orders in which product 103 (p_id = 103) included 103 (p_id = 103) . That is, for example, if a person bought goods 105, 106, 110, 103 , this order is also considered, because it contains goods 103. Returned will be avg of all these orders.

Closed due to the fact that the subject of the question is not clear to Nicolas Chabanovsky ♦ Jan 18 '17 at 9:16 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • one
    It does not solve homework for someone, and help to figure out if something does not work. what have you tried. first get only the ID of the required order from the OrderItem, do you get such a request? - Mike
  • Um ... what are "avg orders"? - Akina
  • @Akina, average - Vadim Prokopchuk
  • And the structure of the tables can be? - Vadim Prokopchuk
  • That avg is average, I know. And mean, sorry, what exactly? contract numbers? number of items? amounts? payment dates? - Akina

2 answers 2

Well, make a selection on orders, make a right join to them positions with the condition that there is 103. Or, even easier, choose a unique order from the positions that are 103 and take all the orders on these.

    Request

     select distinct IdOrder from OrderItem where IdItem = 103

    We IdItem list of orders in which there is a product with IdItem equal to 103. Next, go to the OrderItem table and look only at those orders that were returned to us by the previous request. Each order is grouped together and joined with the goods table Items , to get the price. As a result, we return IdOrder , i.e. Our key grouping and arithmetic average prices of goods included in this group.

    The final query is as follows:

     select IdOrder, AVG(Item.Price) from OrderItem as t1 inner join Items on Items.Id = t1.IdItem where t1.IdOrder in (select distinct IdOrder from OrderItem where IdItem = 1) group by t1.IdOrder