MySQL SELECT on 2 SELECT result is possible or not

user_12385542 0
Viewed 52

I have 2 tables

1-Table Price with 3 columns:

  • part_id (there is repeat)
  • effective_date (same part_id have different effective_date)
  • part_price

2-Table Usage with 3 Columns:

  • part_id (no repeat)
  • usage_p1
  • usage_p2

I want to get the newest price for parts on p1( p2 same logic);

I have tried to use SELECT to fill out the latest effective_date price and the usage_p1 by:

SELECT part_id, MAX(Effective_date) AS Latest_date 
    FROM Price  
    GROUP BY part_id

SELECT Usage_id FROM Usage WHERE Usage_p1>=1

but how can i proceed with these 2 selected result, or it is just impossible to proceed?

If I have to separate the Usage Table into 2 much simple tables P1 and P2. Table P1 only have 2 columns: part_id and usage_p1.

SELECT  price.part_id, MAX(Effective_date) AS Latest_date, price.part_price,
        P1.usage_pi, usage_P1*part_price AS value_p1
    FROM  Price
    INNER JOIN  P1  ON Price.part_id=P1.part_id
    GROUP BY  P1.part_id

Is this way optimal or kind of wast space? enter image description here

mysql
2 Answers

If I understood correctly, you needed to create a query that gets the max values of the two tables and have them related together:

SELECT 
  T1.Latest_date, T2.Latest_p1, T2.Latest_p2
FROM(
  SELECT MAX(p.Effective_date) AS Latest_date
  FROM Price p
  GROUP BY p.part_id
) AS T1
INNER JOIN(
  SELECT MAX(u.usage_p1) AS Latest_p1, MAX(u.usage_p1) AS Latest_p2
  FROM Usage u
  GROUP BY u.part_id
) AS T2
ON T1.part_id = T2.part_id

This query gets the max values of the two tables by the "part_id". A tip I can give you is that you can add a letter after your table like a variable, I use this practice in my work, and it helps a lot. Anyway. I hope this help you out.

user_125142250

This seems very straight forward so perhaps I haven't understood. First get the latest date by product id , here I do so in a cte, then get the relevant price and usage

DROP table if exists price,`usage`;

create table price(pid int,price int, effective_date date);
create table `usage`(pid int,p1 int,p2 int);

insert into price values
(1,10,'2021-01-01'),
(2,10,'2021-01-01'),
(1,20,'2021-02-01'),
(3,20,'2021-02-01'),
(3,10,'2021-01-01');

insert into `usage` values
(1,1,2),(3,0,10),(2,10,0);

with cte as
(select pid,max(effective_date) effective_date from price group by pid)
select cte.pid,p.price,cte.effective_date,p1,p1*price,p2,p2*price
from cte 
join price p on p.pid = cte.pid and p.effective_date = cte.effective_date
join `usage` u on u.pid = cte.pid;

+------+-------+----------------+------+----------+------+----------+
| pid  | price | effective_date | p1   | p1*price | p2   | p2*price |
+------+-------+----------------+------+----------+------+----------+
|    2 |    10 | 2021-01-01     |   10 |      100 |    0 |        0 |
|    1 |    20 | 2021-02-01     |    1 |       20 |    2 |       40 |
|    3 |    20 | 2021-02-01     |    0 |        0 |   10 |      200 |
+------+-------+----------------+------+----------+------+----------+
3 rows in set (0.001 sec)

BTW usage is a reserved word and needs to be backticked. NB it's better to include sample data and expected output as text in the question since we can use text and cannot use images. Also try to avoid speculative digression in the question it just confuses things.

user_61524000
Related