New to joins, need help 1

[c4n]

Hello,

I'm new to table joins and would need some help with getting this SQL query right.

I have two tables:
1. table clicks
partnerid | date | raw | unique

2. table sales
saleid | partnerid | date | amount


Now let's say I want to find out how many clicks and sales are logged for partnerid "2" between 20th July and 5th August. I tried many queries, like the one below:

SELECT A1.date, A1.raw, A1.unique, SUM(A2.amount)
FROM `clicks` A1, `sales` A2
WHERE A1.partnerid =2 AND A1.date
BETWEEN '2004-07-20' AND '2004-08-05' AND A2.date
BETWEEN '2004-07-20' AND '2004-08-05'
GROUP BY A1.date LIMIT 0 , 30

but can't get it to work. What I basically want is a result like this:

date | raw | unique | amount
----------------------------
2004-07-20 | 123 | 120 | 10.00
2004-07-21 | 59 | 52 | 5.00
2004-07-22 | 130 | 125 | 15.00
2004-07-23 | 223 | 211 | 25.00
etc.

Everything except "amount" is from table 1. "amount" should be the SUM() of all sales on that date.

Any help with this query is much appreachiated!

Thank you!

 

[TonyGroves]

How about something like:
[tt]
SELECT a1.date,a1.raw,a1.unique,SUM(a2.amount)
FROM a1 JOIN a2 USING (partnerid,date)
WHERE
a1.partnerid=2
AND a1.date BETWEEN '2004-07-20' AND '2004-08-05'
GROUP BY a1.date
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[r937]

tony, i think it would be extremely unusual for there to be an exact match between every click and every sale, and if the datatypes are DATETIME and not DATE (which seems reasonable for clicks) then i would say it's next to impossible

i.e. an inner join is probably wrong

not the least because if there are multiple clicks and multiple sales on the same day, you will get cross join effects

c4n, what if there were no clicks on a particular date, but some sales? and vice versa? and what if there were no clicks or sales on a particular day?

the desired result, which summarizes both click data and sales data to the same row, implies that there will be some sort of join, but i would do it with two LEFT OUTER joins, from a generated date value to any clicks on that date, and from the same date to any sales on that date

this is quite tricky to do even with subqueries



rudy

http://r937.com/

SQL Consulting

 

[TonyGroves]

Rudy -

I'm assuming that Table 1 contains the total number of clicks for the partner/day. That seems to be what c4n is implying.

-----
ALTER world DROP injustice, ADD peace;

 

[c4n]

Hello,

Thanks for the replies. I appologize for not being more specific, Rudy is right with his "what if?"s. Let me explain things more:

Table "clicks" contains the TOTAL NUMBER of clicks per partner per day. If it helps to simplify things you may imagine table clicks like this
partnerid | date | numberofclicks
and just select "numberofclicks" from this table instead of "raw" and "unique".

Table "sales" contains info on INDIVIDUAL SALES (amount, da te (not timestamp!), and some other info).

It IS possible that some days there are only clicks and no sales and vice versa (no clicks and some sales). It is possible that there are NO SALES, 1 SALE or MULTIPLE SALES per day.

I tried your code Tony - it is quite close to what I need, but it only returns values if the day had at least one sale or click.

You lost me on the "LEFT OUTER joins" Rudy. It would be great if you could provide a sample of how such a query might look.


I know your time is valuable (and not free) and would like to thank you both again for replying and help.

Best regards,

Klemen aka "c4n"

 

[TonyGroves]

If there are situations where a partner/day might have a record in table 1 but not table 2, or in table 2 but not table 1, and you don't want to exclude any partner/day records, then what you would need is a "full outer join". Unfortunately this is not yet available in MySQL, but as far as I know is scheduled for version 5, which is probably a couple of years away.

However, you probably don't want to wait until then. So, the solution would be quite long-winded and involve a temporary table and a few queries. You could try:[tt]

CREATE TEMPORARY TABLE t (`date` DATE PRIMARY KEY);

INSERT t
SELECT `date`
FROM clicks
WHERE
partnerid=2
AND `date` BETWEEN '2004-07-20' AND '2004-08-05';

INSERT IGNORE t
SELECT `date`
FROM sales
WHERE
partnerid=2
AND `date` BETWEEN '2004-07-20' AND '2004-08-05';

SELECT t.`date`,raw,`unique`,SUM(amount)
FROM
t
LEFT JOIN clicks
ON clicks.partnerid=2 AND t.`date`=clicks.`date`
LEFT JOIN sales
ON sales.partnerid=2 AND t.`date`=sales.`date`
GROUP BY t.`date`;

DROP TABLE t;[/tt]

(Note: grouping by only one non-aggregate field is allowed by MySQL, but if you want to be ISO-compliant, you can say "GROUP BY t.`date`,raw,`unique`")

Not quite as slick as a full outer join, but it's the best I can come up with. Also, you might like to change some of your field names, as DATE and UNIQUE are reserved words.


-----
ALTER world DROP injustice, ADD peace;

 

[c4n]

Hi Tony,

Thank you very much for the code, will try that out. I'll also try to use some tricks in my Perl code and will try to avoid thus query in full...

Thanks,

Klemen