Joins

[craygo]

I have two table called servpay, and roster. They are joined by the field playid, id respectively. I want to include all the people from the roster table in a report even if they have no entries in the servpay table.

 

How would I do the JOIN for this???

 

Thanks

 

Ray

[imbrokn]

select from roster R LEFT JOIN servpay S on R.id = S.playID

[craygo]

is there a need to give the tables aliases??

 

this is what I have in my sql statement

$qx = "SELECT roster.handle AS 'name', roster.real_name AS 'rname', ";
$qx .= "EXTRACT(YEAR FROM servpay.paydate) AS 'YEAR', ";
$qx .= "SUM(IF(servpay.paymonth = 1, servpay.amount, 0)) AS 'JANUARY', ";
$qx .= "SUM(IF(servpay.paymonth = 2, servpay.amount, 0)) AS 'FEBRUARY', ";
$qx .= "SUM(IF(servpay.paymonth = 3, servpay.amount, 0)) AS 'MARCH', ";
$qx .= "SUM(IF(servpay.paymonth = 4, servpay.amount, 0)) AS 'APRIL', ";
$qx .= "SUM(IF(servpay.paymonth = 5, servpay.amount, 0)) AS 'MAY', ";
$qx .= "SUM(IF(servpay.paymonth = 6, servpay.amount, 0)) AS 'JUNE', ";
$qx .= "SUM(IF(servpay.paymonth = 7, servpay.amount, 0)) AS 'JULY', ";
$qx .= "SUM(IF(servpay.paymonth = 8, servpay.amount, 0)) AS 'AUGUST', ";
$qx .= "SUM(IF(servpay.paymonth = 9, servpay.amount, 0)) AS 'SEPTEMBER', ";
$qx .= "SUM(IF(servpay.paymonth = 10, servpay.amount, 0)) AS 'OCTOBER', ";
$qx .= "SUM(IF(servpay.paymonth = 11, servpay.amount, 0)) AS 'NOVEMBER', ";
$qx .= "SUM(IF(servpay.paymonth = 12, servpay.amount, 0)) AS 'DECEMBER' ";
$qx .= "FROM roster LEFT JOIN servpay on roster.id=servpay.playid ";
$qx .= "WHERE roster.title= 'OWNER' ";
$qx .= "GROUP BY YEAR ";
$qx .= "ORDER BY roster.id ";

 

I have it being grouped by year but what happens when I insert a person for the year 2006 everyone's name goes away accept the one person that has paid for 2006.

[craygo]

Nobody cares

 

LOL

[craygo]

I got it to work

 

Thanks