Complicated select 1

[aod2]

Hello, I have a table that has a User_ID, then many columns that have integer values. They are named s1_1 through s1_5, s2_1 through s2_5, s3_1 through s3_5, s4_1 through s4_5 and s5_1 through s5_5. So, in other words, there are 25 columns total with these integer values, and 1 User_ID column. What I need to do with the where clause of my select statement is add up all of the values that are stored in s1_1 through s5_5 and see if they match between two User_IDs. I have figured it out thus far:

select s.User_ID from some_data s, some_data s2 where s.User_ID='1' and s2.User_ID='2' and (this is where I'm stumped).

What would be the best way to add all those up and match between the two? I'm really really hoping that I don't have to do a where (s.s1_1+s.s1_2+s.s1_3...+s.s5_5 = s2.s1_1+s2.s1_2+s2.s1_3...+s2.s5_5). What would be nice is if I could either a) Use some sort of an alias scheme to say something like "s_total = (s.s1_1+s.s1_2+s.s1_3, etc.)" or b) Use some sort of a function to transverse through all of the columns and add them up - like a loop of sorts.

What makes matters worse is it's not an exact match either, I have to match them within + or - 5%, so it would be (aliased): where s_total <= s2_total*1.05 and s_total >= s2_total*.95

You can see where the statement could get extremely unwieldy when doing a + or - 5% match.

Any help on this would be GREATLY appreciated.
TIA

 

[petersJazz]

One solution is to add a column total that adds upp all the 25 columns. But then you have to be careful so that you never changes a column without recalculating the sum.

 

[aod2]

Unfortunately, that's not an option in this database. Any other ideas, tips, or tricks out there?

 

[sleipnir214]

I don't understand what you mean by &quot;match between two User_IDs&quot;. Match to what? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[aod2]

In other words, the table looks like this:

CREATE TABLE some_data (
User_ID varchar(6) NOT NULL default '',
s1_1 int(1) default NULL,
s1_2 int(1) default NULL,
s1_3 int(1) default NULL,
s1_4 int(1) default NULL,
s1_5 int(1) default NULL,
s2_1 int(1) default NULL,
s2_2 int(1) default NULL,
s2_3 int(1) default NULL,
s2_4 int(1) default NULL,
s2_5 int(1) default NULL,
s3_1 int(1) default NULL,
s3_2 int(1) default NULL,
s3_3 int(1) default NULL,
s3_4 int(1) default NULL,
s3_5 int(1) default NULL,
s4_1 int(1) default NULL,
s4_2 int(1) default NULL,
s4_3 int(1) default NULL,
s4_4 int(1) default NULL,
s4_5 int(1) default NULL,
s5_1 int(1) default NULL,
s5_2 int(1) default NULL,
s5_3 int(1) default NULL,
s5_4 int(1) default NULL,
s5_5 int(1) default NULL,
KEY User_ID_key (User_ID),
) TYPE=MyISAM;

What I need to do is add up all the values from s1_1 through s5_5, then make sure that where User_ID='2' match all the added values from s1_1 through s5_5 where User_ID='1'. Does that make more sense?

 

[sleipnir214]

Maybe.

You want to:

Sum all those columss where User_ID = 1.
Sum all those columns where User_ID = 2.
Compare the two sums to see if they're the same.

Right? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[aod2]

Yep, that's exactly correct.

 

[aod2]

Er, that is what I want to do. However, I don't want to have to write out an extremely long select statement if I can help it.

This is the finishing touch on a website I have been working on for months now, and I need a solution soon. Since I need this solution soon, and it is a complicated one, I'll PayPal $20 to the first person who can give me a viable solution that will keep me from having to make a huge nasty select string. Think of it as a virtual &quot;Thanks for your help, here's a case of beer (or whatever you feel like spending it on).&quot;

Thanks again in advance!

 

[sleipnir214]

Here's what I've tried...

I created a table called search:

+----+------+------+------+
| id | foo1 | foo2 | foo3 |
+----+------+------+------+
|  1 |    1 |    2 |    3 |
|  2 |    3 |    2 |    1 |
|  3 |    2 |    4 |    6 |
|  4 |    6 |    4 |    1 |
|  5 |    3 |    6 |    9 |
|  6 |    3 |    3 |    3 |
+----+------+------+------+

I performed the following query against that table:

SELECT
	s1.id,
	s1.foo1 + s1.foo2 + s1.foo3 AS summa1,
	s2.foo1 + s2.foo2 + s2.foo3 AS summa2
FROM
	search s1,
	search s2
WHERE
	s1.id = 1 AND
	s2.id = 2
HAVING
	summa1 = summa2

Given any pair of ids, when the sums of the two rows match, I get one row returned. Otherwise, I get no rows returned.

Is this what you're looking for?
Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[aod2]

Aha.. Very close. However, I need it to just return a User_ID if there is a match, without returning the result of summa1 and summa2. If you can make that happen, then you just earned yourself $20.

 

[sleipnir214]

That can't be done. Not and have it calculate your sums, too.

There's no rule that says you have to use every column you get in a query return. Just ignore the other two columns. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[aod2]

Hmmm. Fair enough. What's your Paypal ID? Also, if you do figure out any other ways to cut the select statement down even more I'd appreciate it!

Thanks!

 

[sleipnir214]

That query is not that complicated. When you are LEFT JOINing 5 tables together and performing aggregation, then it's complicated. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[r937]

&quot;if you do figure out any other ways to cut the select statement down even more I'd appreciate it!&quot;

normalize the table, it's not even in first normal form

your queries will be tons simpler


rudy

http://rudy.ca/