On 25-Jul-2003 Brian Newsham wrote:
> Hi,
>
> I'm working on a PHP based website that loads custom pricing for users
> where they logon.
>
<random snipage ahead>
>
> majver and minver identify which price schedule to use. Each customer has
> a different majver minver combination.
>
> The price schedule where majver=0 and minver=0 is called list price, and
> is the only schedule that has a price for every single item. All the
> other combinations of majver and minver make up an sub-list of items that
> sell at the same discount level. If there is no price for a majver,
> minver combo, an attempt is made to use the price where minver=0,
> otherwise list price is used.
>
> To get the price of one item, I would do the following.
>
> mysql> SELECT * FROM item_price WHERE iid=3 AND majver IN (0,1) AND
> minver IN (0,10) ORDER BY majver DESC, minver DESC;
> +--------+--------+-----+--------+
>| majver | minver | iid | price |
> +--------+--------+-----+--------+
>| 1 | 10 | 3 | 98.81 | <= first choice
>| 1 | 0 | 3 | 91.21 | <= second choice
>| 0 | 0 | 3 | 152.02 | <= last choice
> +--------+--------+-----+--------+
> 3 rows in set (0.00 sec)
>
SELECT *, IF(majver=$major,IF(minver=$minor,2,1),0) AS sortfld
...
ORDER BY sortfld DESC, majver DESC, minver DESC;
> Most of the time I want to get information about more than one item at
> once. However this complicates things. I only want 1 row for each iid,
> specifically the row with the highest majver and minver for a given iid.
>
> EXAMPLES:
>
> mysql> SELECT * FROM item_price WHERE iid IN (3,4) AND majver IN (0,1)
> AND minver IN (0,10) ORDER BY majver DESC, minver DESC;
> +--------+--------+-----+--------+
>| majver | minver | iid | price |
> +--------+--------+-----+--------+
>| 1 | 10 | 3 | 98.81 |
>| 1 | 0 | 3 | 91.21 | <= Need to eliminate these rows
>| 0 | 0 | 3 | 152.02 | <=
>| 0 | 0 | 4 | 49.29 |
> +--------+--------+-----+--------+
> 4 rows in set (0.00 sec)
Order by iid and let PHP suppress the duplicates:
$sql= "SELECT *, ...
ORDER BY iid, sortfld DESC, majver DESC, minver DESC";
$res= mysql_query($sql);
$previd= 0;
echo '<table><caption>Best pricing</caption>';
while ($row= mysql_fetch_array($res)) {
if ($previd != $row['iid']) {
echo '<tr><td>', implode('</td><td>', $row), '</td></tr>';
$previd= $row['iid'];
}
}
echo '</table>';
>
> mysql> SELECT * FROM item_price WHERE iid IN (3,5) AND majver IN (0,1)
> AND minver IN (0,10) ORDER BY majver DESC, minver DESC;
> +--------+--------+-----+--------+
>| majver | minver | iid | price |
> +--------+--------+-----+--------+
>| 1 | 10 | 3 | 98.81 |
>| 1 | 10 | 5 | 89.77 |
>| 1 | 0 | 3 | 91.21 | <= Need to eliminate these rows
>| 1 | 0 | 5 | 82.87 | <=
>| 0 | 0 | 3 | 152.02 | <=
>| 0 | 0 | 5 | 138.11 | <=
> +--------+--------+-----+--------+
> 6 rows in set (0.00 sec)
>
> Is it possible to get a result set with 1 row for each iid specified, and
> the price where the majver and minver are the largest combination for
> each iid?
>
See above, --the problem solves itself.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
