SQL Query returns the last date with additional information from the tables

advertisements

I have 5 tables and columns pertinent to this query:

  • Dogs (ID, CallName, Color, Sex, Chipnumber, BreedID)
  • Breed (ID, Name)
  • Status (ID, Status, OwnedOnPremises)
  • DogsStatus (ID, DogsID, StatusID, StatusDate, Note, ContactsID)
  • Contacts (ID, Name)

I am wanting a result of all dogs and their LATEST status. For a test I am using the following records:

  • Dogs (251, Tank, Fawn, M, 14410784, 23) (266, Bonnie, Brindle, 14964070, 23)
  • Breed (23, Mastiff)
  • Status (3, Sold) (4, Given Away) (7, Purchased) (9, Returned)
  • DogsStatus (29, 251, 3, 2013-10-12, 5) (39, 251, 9, 2013-11-10, 17) (146, 251, 4, 2014-01-10, 7) (40, 266, 7, 2013-10-30, 1)
  • Contacts (1, Person1) (5, Person5) (7, Person7) (17, Person17)

So far I have:

SELECT
    d.CallName, b.Name AS 'Breed', d.Color, d.Sex, d.ChipNumber
FROM
    Dogs d
JOIN
   (SELECT
        DogsID, MAX(StatusDate) as MaxStatusDate
    FROM DogsStatus
    GROUP BY DogsID) mds ON mds.DogsID = d.ID
JOIN
   Breeds b ON b.ID = d.BreedID

This will return 2 unique records (1 for Tank and 1 for Bonnie), but whenever I try to get any other of the DogsStatus and/or Status info, I either return only one dog record, or all 3 of Tanks DogsStatus records.

Thanks in advance.


You'll need to join your MaxStatusDate to the DogsStatus table. That way you will only get the most recent status, in the case where you have multiple statuses. Something like

SELECT
    d.CallName, b.Name AS 'Breed', d.Color, d.Sex, d.ChipNumber
FROM
    Dogs d
innner join DogsStatus ds
  ON d.dogsid = ds.dogs_id
JOIN
   (SELECT
        DogsID, MAX(StatusDate) as MaxStatusDate
    FROM DogsStatus
    GROUP BY DogsID) mds ON mds.DogsID = d.ID
JOIN
   Breeds b ON b.ID = d.BreedID
   AND mds.maxstatusdate = ds.statusdate

Something along those lines.