Jump to content

Archived

This topic is now archived and is closed to further replies.

str8_doughboy

problem with Inner Join and order by

Recommended Posts

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:
[code]

$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";

[/code]

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.

[code]

$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)";

[/code]
I now get 0 results.  I then tried the following:

[code]

$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";

[/code]

Again, 0 results.

Any suggestions?

Thanks!

Dave

PS.  PHP version 4.3.11
      MySql version 4.0.18

Share this post


Link to post
Share on other sites
So this [code]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[/code] 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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.