Help Please

[austinderrick2]

I want to do this

Person 1 | 100 weight | Help = Yes |Animal = Dog
Person 1 | 200 weight | Help = Yes | Animal=Cat
Person 1 | 123 weight | Help = No | Animal = Dog
Person 2 | 120 weight | Help = No | Animal = Cat
Person 2 | 74 weight | Help = Yes | Animal = Cat

Winner Person 300 weight where it selects only people with help as yes and adds the max weight for cat + the max weight for dog for each person.

This is what I have so far.

SELECT oid, MAX(weight) as weight FROM animals WHERE help='yes' GROUP BY name ORDER BY weight DESC LIMIT 0, 30

This selects the person with biggest weight and only when help = yes. I dont know how to do the rest of it.....

Thanks for all replys! 😃

[ratass2002]

Are you using MySql 4? You can use UNION and subselect in that version.

I am still using the latest version 3 so I can't test on my machine.

Something like(just guessing)

SELECT name sum(weight) as FROM TABLE WHERE Name IN ((SELECT name,max(weight) FROM Table WHERE Help='Yes' AND Animal='Dog' group by name) UNION (SELECT name,max(weight) FROM Table WHERE Help='Yes' AND Animal='Cat' group by name) ) HAVING sum(weight)=300

If this work I am so proud of myself😃 Otherwise I will be drepessed for a week:o

[austinderrick2]

SELECT name, sum(weight) as totalweight FROM table WHERE Name IN ((SELECT name,max(weight) FROM Table WHERE help='yes' AND type='moose' group by name) UNION (SELECT name,max(weight) FROM table WHERE help='yes' AND type='elk' group by name) )

I edited it a little to work for my use but it still didnt work. It gave me this error.

"You have an error in your SQL syntax near 'SELECT name, max(weight) FROM Table WHERE help='yes' AND type='moose' group by' at line 1"

😕

I took out the:
HAVING sum(weight)=300

because I want it to automatically figure out the 300 part. 😉 That way when people enter a higher number it doesnt still select the 300 😉

[ratass2002]

---my friend's try
SELECT a.name

FROM table a

WHERE a.help = 'yes' AND

(SELECT Max(weight)

FROM table b

WHERE b.Animal = CAT and b.name = a.name)

+

(SELECT Max(weight)

FROM table c

WHERE c.Animal = DOG and c.name = a.name)

= 300

---modification from me:
SELECT name

FROM table a

WHERE help = 'yes' AND

(SELECT Max(weight)

FROM table b
WHERE b.Animal = CAT GROUP BY b.name)

+

(SELECT Max(weight)

FROM table c
WHERE c.Animal = DOG GROUP BY c.name)

= 300

I like this one 😃 I hope it works, this question is really making me want to install MySql 4.

[austinderrick2]

= 300

no 300 lol.... It is supposted to add that automatically lol....

I havent had a chance to try out the script since I'm not at home right now but I'll try it as soon as I get home.😃 Also there is only one table.... Should I load the table more than once or what?

[ratass2002]

a, b and c are just alias for the same table.
subselect goes against all rows in the main SELECT so to make things clear table aliases are created.

Logic is correct here, just I am not sure if the syntax is correct.

[austinderrick2]

Still an error... I'll post my table text so that you can see for yourself what my table looks like.

# phpMyAdmin MySQL-Dump
# version 2.2.1-dev
# [url]http://phpwizard.net/phpMyAdmin/[/url]
# [url]http://phpmyadmin.sourceforge.net/[/url] (download page)
#
# Host: members-free-php01.uk.db.lyceu.net:3309
# Generation Time: Feb 20, 2003 at 10:41 PM
# Server version: 3.23.33
# PHP Version: 4.0.6
# Database : `lakesidecabin8_uk_db`
# --------------------------------------------------------

#
# Table structure for table `trophys`
#

CREATE TABLE trophys (
  oid int(11) NOT NULL auto_increment,
  name text NOT NULL,
  email text NOT NULL,
  gender text NOT NULL,
  weight int(11) NOT NULL default '0',
  location text NOT NULL,
  weapon text NOT NULL,
  scope text NOT NULL,
  distance text NOT NULL,
  credits text NOT NULL,
  bonus text NOT NULL,
  worldrecord text NOT NULL,
  time text NOT NULL,
  date text NOT NULL,
  type text NOT NULL,
  PRIMARY KEY  (oid),
  UNIQUE KEY oid (oid)
) TYPE=MyISAM;

#
# Dumping data for table `trophys`
#

INSERT INTO trophys VALUES (1,'Derrick','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','elk');
INSERT INTO trophys VALUES (2,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','caribou');
INSERT INTO trophys VALUES (3,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','moose');
INSERT INTO trophys VALUES (4,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','blackbear');
INSERT INTO trophys VALUES (5,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','brownbear');
INSERT INTO trophys VALUES (6,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','bighornsheep');
INSERT INTO trophys VALUES (7,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','mountaingoat');
INSERT INTO trophys VALUES (8,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','mountainlion');
INSERT INTO trophys VALUES (9,'Derrick Austin','[e-mail]','Male',100,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','wolf');
INSERT INTO trophys VALUES (10,'Derrick Austin','[e-mail]','Female',150,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','wolf');
INSERT INTO trophys VALUES (11,'Derrick','[e-mail]','Male',120,'NOWHERE','50mm','None','125','25','25','Yes','Noon','1-12-03','elk');

I want to select the person with the highest score of male aniamls by weight and only the max of each type.

[ratass2002]

Holy macarroni what a table😃

I installed MySql 4 now and it's not working😃😃

I found out that subselect might not be implemented in version 4
only in version 4.1 and later. So you might want to test it against other DB. I will try on MSSQL 2000 tonight.

[ultraslacker]

Your version of mysql does not support subselects.

[austinderrick2]

What version of mySQL do I have? I think it says in that table thing but I wouldnt know what number to look at....

[austinderrick2]

Your version of mysql does not support subselects.

Did you mean my version of mySQL 🙁 ? Is there any other way to do it? 🙁

[austinderrick2]

??? 😕

[ultraslacker]

Your version is listed in mysqldump. You can get version info via mysqladmin version.

Besides upgrading to something that does support subselects, you can either use temporary tables or application code to select out the results desired.

[austinderrick2]

What code do I need to do that? (I'm kinda new at this lol)

(Exact code is perferable 😉 I can edit most of it if it is wrong but not always... :S )

[Lars_Berg]

select t1.oid,sum(t1.weight) from trophys t1 left join trophys t2
  on t1.oid = t2.oid 
 and t1.type = t2.type
 and t1.weight > t2.weight
  where t2.oid is null
   and t1.help = 'Yes'
   and t.type in ('Cat','Dog')
 order by 2 desc limit 1

I don't recognize the columns in your first post as compared to your table definition. What is help and is type the same as animal?

[austinderrick2]

help = gender (wasnt too sure how strict the admins were here but I read some posts and relized that would be ok)

[austinderrick2]

Errors still 🙁

Since I don't have anything of inportance on here right now. I trust you guys with this. Try to get it to work right on the database there. To access it go to

http://www.tripod.lycos.co.uk/build/php4u/admin/index.phtml

username:lakesidecabin8
password:derrick

Then you should be able to run mySQL querys with that.....I hope that you guys can get the query to work right 🙁

[austinderrick2]

?😕

[austinderrick2]

does anyone know? ??? 😕