having some difficulty with a query

[Anon]

Hi, I am just learning php and mysql and am having a little difficulty. (Forgive me if this question seems stupid.)

I have three tables - table1, table2, table3 created thusly:

create table1 (
t1_autoid int(11) not null auto_increment,
clicks int(11),
Xid int(11),
primary key(t1_autoid));

create table2 (
t2_autoid int(11) not null auto_increment,
turnover int(11),
Yid int(11),
primary key(t2_autoid));

create table3 (
t3_autoid int(11) not null auto_increment,
Xid int(11),
Yid int(11),
primary key(t3_autoid));

The tables are populated with the following:

table1 is populated:
insert into table1 values (NULL, 10, 1111);
insert into table1 values (NULL, 7, 1111);

table2 is populated:
insert into table2 values (NULL, 100, 1);
insert into table2 values (NULL, 200, 1);

table3 is populated:
insert into table3 values (NULL, 1111, 1);

Here is my query:
SELECT sum(table1.clicks) AS clicks, sum(table2.turnover) AS turnover
FROM table1, table2, table3
WHERE table3.Xid=table1.Xid and table3.Yid=table2.Yid and table3.Xid=1;

What I am trying to return is a single row with the values of 17 for clicks and 300 for turnover. What I am winding up with is 34 clicks and a turnover of 600. Can somebody help me out with this? What is the proper sql statement I should be using to get the output I want?

[Anon]

The final part of the where clause "...and table3.Xid=1" - The value that table3.Xid could be is any integer and will be populated through a php variable when I script this.

[sarahk]

You need to include
order by
group by

so that the result rows are put in the right order and then the duplicates removed.

I always recommend using a query tool to help iron out the problems and display example SQL. MS Access has the most readily available one. You'll need to dummy up your tables but that shouldn't take too long.