Puzzle

[manalnor]

Hello dear friends,

 

My mind no longer able to think about it so please help me

 

let say we have 2 database tables

 

Table_one (id,user,code) example user can post many codes

 

(1,user1,code1)

(2,user1,code2)

(3,user2,code3)

(4,user3,code4)

 

Table_Two (id,code,hits) example

 

(1,code1,100)

(2,code2,50)

(3,code3,40)

(4,code4,80)

 

 

the common between both is the code but i wanna say Get me the

 

sum of hits - of - all codes that is only for user1 

 

 

i did tried this but didn't works logically

 

maybe by saying

$sql = "SELECT COUNT(hits) AS totalhits FROM table1,table2 Where table2.code=table1.code and table1.code=?????????";
$cus= mysql_query($sql) or die($sql);
$count = mysql_fetch_array($cus);

 

if we know how to say instead of ????????  the following;

 

and table1.code added by user1

 

maybe we can get it right can we really get it

 

thanks

 

results should be totalhist = 150

[jcbones]

Try SUM instead of COUNT.

 

$sql = "SELECT SUM(table2.hits) AS totalhits FROM table1 JOIN table2 ON table2.code=table1.code WHERE table1.user='user1'";

[manalnor]

:'( didn't worked cause no attachment, it will shows only the total of added codes at both table1 and table2

 

 

and the puzzle is to get the total hits of codes added in table 2 which is the same codes that only and only added by user1 at table1

 

[ignace]

The query of jcbones works. If it didn't means your information is wrong.

 

Here's a test-case for you to try out:

create temporary table t1 (user CHAR(5) NOT NULL, code CHAR(5) NOT NULL PRIMARY KEY);
create temporary table t2 (code CHAR(5) NOT NULL PRIMARY KEY, hits TINYINT NOT NULL);
insert into t1 values ('user1', 'code1'), ('user1', 'code2'), ('user2', 'code3'), ('user3', 'code4');
insert into t2 values ('code1', 100), ('code2', 50), ('code3', 40), ('code4', 80);
select sum(t2.hits) from t1 join t2 using(code) where t1.user='user1';

 

Running this, returns 150