UKSgvAkG

1use foo_db;
2
3drop table if exists foo;
4
5create table foo
6(
7foo_id int unsigned not null auto_increment primary key,
8name varchar(64)
9);
10
11insert into foo (name) values ('name1'),('name2'),('name3');
12
13drop table if exists foo_price;
14
15create table foo_price
16(
17price_id int unsigned not null auto_increment primary key,
18foo_id int unsigned not null,
19unit_price decimal(10,2) not null default 0 -- use decimal for money data type
20);
21
22insert into foo_price (foo_id, unit_price) values (1, 1.00), (1, 1.10), (1, 1.20), (2, 2.00), (2, 2.10), (3, 3.00);
23
24select * from foo;
25
26/*
27foo_id *	name
28--------------
291				name1
302				name2
313 				name3
32*/
33
34select * from foo_price;
35
36/*
37price_id *	foo_id *	unit_price *
38----------------------------------
391						1					1
402						1					1.1
413						1					1.2						<---- want this price for foo_id = 1
424						2					2
435						2					2.1						<---- want this price for foo_id = 2
446						3					3							<---- there is only one price for foo_id = 3
45*/
46
47select
48 f.foo_id,
49 f.name,
50 max(fp.price_id) as price_id,
51 max(fp.unit_price) as unit_price
52from
53 foo f
54inner join foo_price fp on f.foo_id = fp.foo_id
55group by
56 fp.foo_id
57order by
58 f.foo_id;
59
60/*
61
62foo_id *	name	price_id	unit_price
63------------------------------------
641					name1	3					1.2
652					name2	5					2.1
663					name3	6					3
67*/