Jump to content


Photo

problem with Inner Join and order by


  • Please log in to reply
2 replies to this topic

#1 str8_doughboy

str8_doughboy
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 06 October 2006 - 01:30 AM

Hi All, 

Tearing my hair out over what seems like it should be a simple thing to do.

One of my databases has two tables:

Donors
Donations

The Donors table contains a donor id, First Name, Last Name, Address, etc about the donors.

The Donations table contains a donation id, donor id, donation amount, month, day, year, and other misc information about the individual donation.

I have a php script that downloads and inner join of the two tables into an excel spreadsheet.

Here's the section I'm having trouble with:

$select = "SELECT Donors.DID, Donors.DFirstName, Donors.DLastName, 
Donations.DonMonth, Donations.DonDay, Donations.DonYear, 
Donations.DonAmount FROM Donations INNER JOIN Donors ON 
Donations.DonId = Donors.DID ORDER By Donors.DLastName";


This works fine as it is... however in addition to being able to order by Donors.DLastName, i'd also like the results sorted by date (most recent to furthest back).

I tried altering the above code as below.


$select = "SELECT Donors.DID, Donors.DFirstName, 
Donors.DLastName, Donations.DonMonth, Donations.DonDay, 
Donations.DonYear, Donations.DonAmount FROM Donations 
INNER JOIN Donors ON Donations.DonId = Donors.DID ORDER By 
Donors.DLastName (ASC), Donations.DonYear (DESC), 
Donations.DonMonth (DESC), Donations.DonDay (DESC)";

I now get 0 results.  I then tried the following:


$select = "SELECT Donors.DID, Donors.DFirstName, Donors.DLastName, 
Donations.DonMonth, Donations.DonDay, Donations.DonYear, Donations.DonAmount 
FROM (SELECT * FROM Donations ORDER By DonId (ASC), DonYear(DESC),DonMonth(DESC),
DonDay(DESC)) INNER JOIN Donors ON Donations.DonId = Donors.DID ORDER By Donors.DLastName";


Again, 0 results.

Any suggestions?

Thanks!

Dave

PS.  PHP version 4.3.11
      MySql version 4.0.18


#2 jcarouth

jcarouth
  • New Members
  • Pip
  • Newbie
  • 4 posts
  • LocationSan Jose, CA

Posted 06 October 2006 - 02:40 AM

So this
SELECT
  Donors.DID
  , Donors.DFirstName
  , Donors.DLastName
  , Donations.DonMonth
  , Donations.DonDay
  , Donations.DonYear
  , Donations.DonAmount
FROM
  Donations
INNER
  JOIN
    Donors
      ON Donations.DonId = Donors.DID
ORDER
  BY
    Donors.DLastName ASC
    , Donations.DonYear DESC
    , Donations.DonMonth DESC
    , Donations.DonDay DESC
won't give you results?

In the future, you can consolidate those date fields into one column of type date or datetime and extract the specific part you want when necessary.

#3 str8_doughboy

str8_doughboy
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 06 October 2006 - 11:29 AM

That fixed it, apparantly it was choking on the ( ) around the ASC and DESC!!!

Thanks much for your help!

Dave ;D




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users