Mysql Query Sollution

Databases
1

Hello everyone.
I would like your help in the following problem

I have a table PRODUCT PRICES
there are some values that are based on the DEFAULT SIZE and TYPE
but there are values depending on whether another variable is set CATEGORY

My problem is that I want to do a query that returns me the DEFAULT values if any
value is set and a class then only brings this designated category

Example


SELECT

sp_sizeid,
sp_typeid,
sp_default,
sp_groupid

FROM
shop_prices

WHERE
sp_groupid = '0 'OR
sp_groupid = '36209624 '

THE ABOVE Returns


1,1,1,0
1,2,1,0
2,1,1,0
2,2,1,0
1,1, null, 362

The first record and the last have the same SIZE & TYPE [1,1] and in this case I only bring what the groupid is not 0 [,x the last value]

2

MAX(sp_groupid) ?

3

Hello Guido!

The ΜΑΧ return only the first value [1,1,1,0]

i want το return this

------ [delete this values 1,1 same with last value]
1,2,1,0
2,1,1,0
2,2,1,0
1,1, NULL, 362

4

not when i tested it…

CREATE TABLE test_prices
( sp_sizeid  INTEGER
, sp_typeid  INTEGER
, sp_default INTEGER
, sp_groupid INTEGER
);
INSERT INTO test_prices VALUES
 (1,1,1,0)
,(1,2,1,0)
,(2,1,1,0)
,(2,2,1,0)
,(1,1, NULL, 362)
;
SELECT sp_sizeid  
     , sp_typeid  
     , MAX(sp_default)  
     , MAX(sp_groupid)
  FROM test_prices
GROUP
    BY sp_sizeid  
     , sp_typeid

and the results:


[COLOR="Red"]1,1,1,362[/COLOR]
1,2,1,0
2,1,1,0
2,2,1,0
5

Thanks very match r937!

CREATE TABLE `shop_prices` (
  `sp_id` int(11) NOT NULL auto_increment,
  `sp_sizeid` int(11) default NULL,
  `sp_typeid` int(11) default NULL,
  `sp_price` double default NULL,
  `sp_default` int(11) default NULL,
  `sp_groupid` int(11) default '0',
  `sp_enable` int(11) default '1',
  `sp_datetime` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`sp_id`),
  UNIQUE KEY `sp_sizeid` (`sp_sizeid`,`sp_typeid`,`sp_groupid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


INSERT INTO `shop_prices` VALUES ('1', '1', '1', '1', '1', '0', '1', '2010-11-07 17:13:54');
INSERT INTO `shop_prices` VALUES ('2', '1', '2', '1.2', '1', '0', '1', '2010-11-07 17:13:54');
INSERT INTO `shop_prices` VALUES ('3', '2', '1', '2', '1', '0', '1', '2010-11-07 17:13:54');
INSERT INTO `shop_prices` VALUES ('4', '2', '2', '2.2', '1', '0', '1', '2010-11-07 17:13:54');
INSERT INTO `shop_prices` VALUES ('5', '3', '1', '3', '1', '0', '1', '2010-11-07 17:13:55');
INSERT INTO `shop_prices` VALUES ('6', '3', '2', '3.2', '1', '0', '1', '2010-11-07 17:13:55');
INSERT INTO `shop_prices` VALUES ('8', '1', '1', '500', null, '36209624', '1', '2010-11-07 17:26:28');

Query


SELECT
 
sp_sizeid,
sp_typeid,
MAX(sp_default),
MAX(sp_groupid),
MAX(sp_price) as sp_price

FROM
shop_prices

WHERE
shop_prices.sp_groupid =  '0' OR
shop_prices.sp_groupid =  '36209624'

GROUP BY
sp_sizeid, sp_typeid

Results


1,1,1,36209624,500
1,2,1,0,1.2
2,1,1,0,2
2,2,1,0,2.2
3,1,1,0,3
3,2,1,0,3.2

NOW the problem is the first value with price (500) has sp_ID (8) and not (1) it is possible to fix this?

6

Where did that price come from?

And you aren’t selecting the sp_ID in your query, so what is there to fix?

7

Here the correct query

SELECT
shop_prices.sp_id,
shop_prices.sp_sizeid,
shop_prices.sp_typeid,
shop_prices.sp_price,
Max(shop_prices.sp_groupid)

FROM
shop_prices
WHERE
shop_prices.sp_groupid =  '0' OR
shop_prices.sp_groupid =  '36209624'
GROUP BY
sp_sizeid, sp_typeid

Problems

The sp_price and the sp_id is not from the record with id 8

the sp_groupid is always greater from default values
but the
sp_price is not always greater may be smaller from default values 0>1000

Default values is sp_default=1 and sp_groupid = 0

8

this was not part of your original requirements

you just did the same to us as those silly clients do to you – they give you something to work on, you solve it, then they say “no, that’s not right”

your original request was [indent]I want to do a query that returns me the DEFAULT values if any value is set and a class then only brings this designated category[/indent]you did not say anything about returning anything from the “record with id 8” or any other id

9

The answers you give very useful.
I made the above question as part of the problem.

Wanted from the beginning when the result comes
be in the form below.


id,size,type,default,group,price
8,1,1,0,36209624,500
2,1,2,1,0,1.2
3,2,1,1,0,2
4,2,2,1,0,2.2
xxxxxxx
xxxxxxx

namely to the values present in the '362 'which is the id ‘8’ as a conclusion.

Thank you.