Subquery Postgres: two tables not directly related

Question

plates

plate_id	date	product_id
1	01-12-2020	101
2	01-12-2020	202
3	02-12-2020	101
4	02-12-2020	202
5	02-12-2020	303

production

order_id	date	product_id	cost
1001	01-12-2020	101	10.95
1002	01-12-2020	202	19.00
1003	02-12-2020	101	11.50
1004	02-12-2020	202	20.05
1005	02-12-2020	303	17.00

the result of the query I need to get is:

plate_id	cost
4	20.05

Their are not related with pk and fk,

each product can have more then one production cost.

I suppose that I need to create some subquery where to take into account always two columns: date and product_id

below query unfortunatelly gives an error

SELECT pl.plate_id, pr.cost
FROM plates pl
JOIN (select * from production pr where pr.date=pl.date and pr.product_id=pl.product_id)
WHERE pl.product_id = 4;

Why 20.05 instead of 19.00? Are you ordering by latest date or highest cost? — Adrian Klaver
– Adrian Klaver, Commented Dec 14, 2020 at 21:02

GMB · Accepted Answer · 2020-12-14 21:11:10Z

1

You seem to want a join on product_id and date:

select pl.plate_id, pr.cost
from plates pl
inner join production pr using (product_id, date)
where pl.plate_id = 4

answered Dec 14, 2020 at 21:11

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kubaSpolsky Over a year ago

in my real case I had to write: inner join mrp_production pr on (pr.product_id=pp.id, pr.date_planned_start::date=m.date) but I get following error: argument of JOIN/ON must be type boolean, not type record

GMB Over a year ago

@kubaSpolsky. So: inner join mrp_production pr on pr.product_id=pp.id and pr.date_planned_start::date=m.date

Collectives™ on Stack Overflow

Subquery Postgres: two tables not directly related

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related