i have a table contains items units details (st_items_units) like :-
| id |
stitems_ID |
unit |
stitemsu_UnitNum |
unit price |
| 1 |
1 |
u1 |
1 |
2 |
| 2 |
1 |
u2 |
10 |
20 |
| 3 |
1 |
u3 |
100 |
200 |
| 4 |
2 |
u1 |
1 |
5 |
| 5 |
2 |
u2 |
12 |
60 |
| 6 |
3 |
u1 |
1 |
10 |
| 7 |
4 |
u1 |
1 |
3 |
| 8 |
4 |
u3 |
12 |
36 |
| 9 |
4 |
u4 |
100 |
300 |
as the table every item may be have only one unit or 2 unit or 3 unit or more.
i want to create 2 view :
1- to select the two smallest unit (if there is one unit for the item , repeat the only one ). as this table :
| stitems_ID |
SmallUnit |
LargeUnit |
SmallUnitPrice |
LargeUnitPrice |
UnitNum |
| 1 |
u1 |
u2 |
2 |
20 |
10 |
| 2 |
u1 |
u2 |
5 |
60 |
12 |
| 3 |
u1 |
u1 |
10 |
10 |
1 |
| 4 |
u1 |
u3 |
3 |
36 |
12 |
2- to select the smallest unit & the largest Unit (if there is one unit for the item , repeat the only one ). as this table :
| stitems_ID |
SmallUnit |
LargeUnit |
SmallUnitPrice |
LargeUnitPrice |
UnitNum |
| 1 |
u1 |
u3 |
2 |
200 |
100 |
| 2 |
u1 |
u2 |
5 |
60 |
12 |
| 3 |
u1 |
u1 |
10 |
10 |
1 |
| 4 |
u1 |
u4 |
3 |
300 |
100 |