Jump to content


Photo

Joins


  • Please log in to reply
4 replies to this topic

#1 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 September 2005 - 06:53 PM

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

#2 imbrokn

imbrokn
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 23 September 2005 - 07:10 PM

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

#3 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 September 2005 - 07:15 PM

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.

#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 September 2005 - 08:01 PM

Nobody cares :(

LOL

#5 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 September 2005 - 08:27 PM

I got it to work

Thanks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users