Join with 2 counts from 3 tables, doesn't it work

[cafrow]

I am very supprised to by the results that I get from the query. I have a site where the client wanted simple reporting by date for the views and clicks of banners on their site. So i setup 2 tables, one for views and one for clicks where I store the deal_id, date and then an ID field, when I pull the report I am trying to pull the deal ID, its name and then the clicks and views that go along with it. here are my tables.

[deals]
-id
-name
-business_id

[deal_views]
-id
-deal_id
-date

[deal_clicks]
-id
-deal_id
-date

Here is the query I am running.



SELECT COUNT(deal_views.id) AS `views`, COUNT(deal_clicks.id) AS `clicks`, deals.name, deals.id FROM `deals` LEFT JOIN deal_views ON deals.id = deal_views.deal_id LEFT JOIN deal_clicks ON deals.id = deal_clicks.deal_id WHERE deals.business_id = 1 GROUP BY deals.id ORDER BY deals.name ASC




My results for views and clicks are both 62116, the clicks table has a total of 158 records and my views has 1153, so that does not add up at all. If i remove either the views or the clicks then it works fine. I am just wondering what I am doing wrong.

Thanks

[russell]

you aren't grouping properly. should be a syntax error actually. also, if it is assumed that a record cant exist in views and clicks table unless it exists in deals, then u dont need to left join, can use inner join.

SELECT Count(v.id) as views, Count(c.id) as clicks, d.name, d.id
FROM deals as d
JOIN deal_views as v
On v.deal_id = d.id
JOIN deal_clicks as c
On c.deal_id = d.id
WHERE d.id = 1
GROUP BY
d.name, d.id
ORDER BY
d.name

[cafrow]

Thanks for the help on that one. I need to probably read up on joins, I learned how to do them when customizing oscommerce code, so I am sure I don't really understand them that well.

Thanks for the help again.

[MrCoder]

I try to avoid using joins since they always seem to be much slower then selecting from multiply tables and filtering with where, for example.


SELECT
Count(v.id) as views,
Count(c.id) as clicks,
d.name,
d.id
FROM
deals as d,
deal_views as v,
deal_clicks as c
WHERE
v.deal_id = d.id
AND
c.deal_id = d.id
AND
d.id = 1
GROUP BY
d.name, d.id
ORDER BY
d.name


I am by no means a SQL pro, is there any downside to doing it this way?

[potterd64]

I think inner joins are a bit cleaner than joining everything in the where clause. It makes the statements easier to read for me. I don't think it makes too much difference because the database should optimize your query before it gets executed anyways.

[MrCoder]

Well I had one join based query that took over 60 seconds using joins, but only took 5 seconds without them.

This was in the oscommerce admin order management system.

[NightShift58]

I try to avoid using joins...
SELECT
Count(v.id) as views,
Count(c.id) as clicks,
d.name,
d.id
FROM
deals as d,
deal_views as v,
deal_clicks as c
WHERE
v.deal_id = d.id
AND
c.deal_id = d.id
AND
d.id = 1
GROUP BY
d.name, d.id
ORDER BY
d.name
According to the good people at MySQL, this is a join.

But it makes for much nicer reading and I use this format as well - whenever I can.

[MrCoder]

According to the good people at MySQL, this is a join.

But it makes for much nicer reading and I use this format as well - whenever I can.

How come I got such a huge speed increase when I replaced the join version with the where/and version then?

[potterd64]

it depends on what database software you are using. Do you know what database the oscommerce admin order management system uses?

[MrCoder]

it depends on what database software you are using. Do you know what database the oscommerce admin order management system uses?

MySQL