Need help with JOIN & AS SUM

[Chrysanthus]

Hi all.

I'm new at this with JOIN AND AS and sow on, sow here i'am with my problems.

The thing i need help with to list my values in the right ORDER
but the problem is that i have 2 tabels i get values from and i want to order by a vots from many diffrent voters.

my tabels look like this

Tabel: userlogbook

rows.

ID
lbkownerID
adminlevel
lbktopic
lbkpost
showlbk
lbktimestamp

Tabel: logbook_log

rows.

ID
lbkID
lbkownerID
lbkvisitorID
lbkvisitorview
lbkpoll
lbkvisitorstamp

Step 1.

Match ID with lbkID sow it is matches

Step 2.

Make a SUM on all inserts in row lbkpoll where row lbkID,lbkownerID AND lbkvisitorID matches

Step 3.

Liste right values after the right SUM.

i have manage to make something like this sow far but it was just i ges and it dosent work.

I have tryed to read the help on http://www.mysql.com regarding JOIN but i dident find any like my senarios sow it dident help with that 🙁.

<?php
$sql = "SELECT userlogbook.*,logbook_log.*, SUM(logbook_log.lbkpoll) AS lbkpoll_tot FROM userlogbook LEFT JOIN logbook_log ON userlogbook.ID = logbook_log.lbkID WHERE userlogbook.showlbk='yes' ORDER BY lbkpoll_tot DESC";?>

(

[TheWiz]

you have two problems here:
1) All SQL functions (sum,avg,...) require a GROUP BY clause. This clause is almost always made up from everything in your SELECT clause, before the function.
2) GROUP BY clauses do not accept wild cards, therefore you will need to itemize all the fields that you want returned in your SELECT clause, and repeat the same list in your GROUP BY clause

SELECT a.somefield, b.somefield, SUM(b.lbkpoll) AS lbkpoll_tot
FROM userlogbook a
LEFT JOIN logbook_log b
ON userlogbook.ID = logbook_log.lbkID
WHERE a.showlbk='yes'
GROUP BY a.somefield,b.somefield
ORDER BY lbkpoll_tot DESC";?>

Note: using aliases for table names can save a lot of typing. Two or three letter abreviations can make the aliases more readable, but I usually just use a,b,c,...

[Chrysanthus]

sorry for asking but i didient userstand this really.

a.somefield, b.somefield

a is the tabel or a aliases?

can this work

SELECT a., b., SUM(b.lbkpoll) AS lbkpoll_tot
FROM userlogbook a
LEFT JOIN logbook_log b
ON userlogbook.ID = logbook_log.lbkID
WHERE a.showlbk='yes'
GROUP BY a.,b.
ORDER BY lbkpoll_tot DESC