Jump to content

problem with Inner Join and order by


str8_doughboy

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
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.