Help with MySQL joins

kandon

I am trying to join three MySQL tables in the same DB and I am having some difficulty.
I have separated this into two SQL statements that work. Can someone help me put them together?

This one gives me the totals for each affiliate and the join gives me the affiliate name and company:

"SELECT SUM(lineitemtotal), affiliateid, users.namefirst, users.namelast, orders.site FROM orders LEFT JOIN users ON orders.affiliateid=users.id WHERE orders.affiliateid > 0 AND orders.site = "bet" GROUP BY affiliateid;"

This one gives me a count of clicks for each affiliate. This is the one I want to join to the one above:

'SELECT aid, COUNT(*) FROM affiliatelog WHERE site = "bet" GROUP BY aid;'

I need to join the second statement to the first.

jlaplante

I believe you can just add another LEFT JOIN statement after your first LEFT JOIN statement and join on orders.site = affiliatelog.site.

The only problem I can see is that you have 2 GROUP clauses...not sure you can do 2 seperate GROUPS in the same SQL statement.

tomhath

See if this works, I haven' t tested it.

You should list all the selected fields in the GROUP BY, mysql lets you be sloppy but its not a good practice.

SELECT SUM(lineitemtotal), COUNT(affiliatelog.site),
affiliateid, users.namefirst, users.namelast, orders.site
FROM orders
LEFT JOIN users
ON orders.affiliateid = users.id
LEFT JOIN affiliatelog
ON orders.affiliateid = affiliatelog.aid
WHERE orders.affiliateid > 0
AND orders.site = "bet"
GROUP BY affiliateid, users.namefirst, users.namelast, orders.site

kandon

this doesn't cause an error, but apparently causes an endless loop. watching processes, i see mysqld jump up to about 40% and stay there until.

ultraslacker

Prefix the query with EXPLAIN and see if you need to add indexes.

kandon

Here are the results of the EXPLAIN statement:

type=ALL
possible_keys=AffiliateID
key=
key_len=
ref=
rows=1044
Extra=Using where; Using temporary; Using filesort

table=users
type=eq_ref
possible_keys=PRIMARY
key=PRIMARY
key_len=4
ref=orders.AffiliateID
rows=1
Extra=

table=affiliatelog
type=ref
possible_keys=aid
key=aid
key_len=5
ref=orders.AffiliateID
rows=18
Extra=