Hello, I'm sorry I couldn't think up a more precise subject line.
I would like to know whether the following can be implemented in SQL:
The example below joins 4 tables ITEM, BRAND, MODEL and CONDITION. In human understandable terms: a [secondhand] Item is of a particular Model and Brand. The Items retail at different prices depending on their Condition.
Required result set:
Brand | Model | Cond | Cond | Price | Price
| | min | max | min | max
-------------------------------------------
Canon | A1 | Exc | Mint | 139 | 155
Canon | F1N | Exc++| Mint-| 329 | 379
Canon | 24mm | Exc--| Mint+| 99 | 179
Nikon | 50mm | Exc--| Mint+| 109 | 119
This is *almost* what I need:
SELECT BRAND.BRAND_NAME, MODEL.MODEL_NAME, min (ITEM.PRICE),max (ITEM.PRICE) *min (CONDITION.POSITION),max (CONDITION.POSITION)* FROM ITEM left outer join MODEL on MODEL_PK =ITEM.MODEL_FK left outer join BRAND on BRAND.BRAND_PK =MODEL.BRAND_FK left outer join CONDITION on CONDITION.CONDITION_PK = ITEM.CONDITION_FK group by BRAND.BRAND_NAME,MODEL.MODEL_NAME
Problem: Table CONDITION has the columns - NAME varchar(5) - POSITION int2
In the result I don't want min/max(POSITION) but CONDITION.NAME of min and max(POSITION) for each MODEL.
Is this possible at all? --
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
