In SQL how do I write a query to return 1 record from a 1 to many relationship?

Let's say I have a Person table and a Purchases table with a 1 to many relationship. I want to run a single query that returns this person and just their latest purchase.开发者_JS百科 This seems easy but I just can't seem to get it.

select p.*, pp.*
from Person p
left outer join (
    select PersonID, max(PurchaseDate) as MaxPurchaseDate
    from Purchase
    group by PersonID
) ppm
left outer join Purchase pp on ppm.PersonID = pp.PersonID
    and ppm.MaxPurchaseDate = pp.PurchaseDate
where p.PersonID = 42

This query will also show the latest purchase for all users if you remove the WHERE clause.

Assuming you have something like a PurchaseDate column and want a particular person (SQL Server):

SELECT TOP 1 P.Name, P.PersonID, C.PurchaseDescription FROM Persons AS P 
INNER JOIN Purchases AS C ON C.PersonID = P.PersonID
WHERE P.PersonID = @PersonID
ORDER BY C.PurchaseDate DESC

Many Databases preform the "Limit or Top" command in different ways. Here is a reference http://troels.arvin.dk/db/rdbms/#select-limit and below are a few samples

If using SQL Server

SELECT TOP 1
  *
FROM Person p
INNER JOIN Purchases pc on pc.PersonID = P.PersonID
Order BY pc.PurchaseDate DESC

Should work on MySQL

SELECT
  *
FROM Person p
INNER JOIN Purchases pc on pc.PersonID = P.PersonID
Order BY pc.PurchaseDate DESC
LIMIT 1

Strictly off the top of my head!...If it's only one record then...

SELECT TOP 1 *
FROM Person p
   INNER JOIN Purchases pu
   ON p.ID = p.PersonId
ORDER BY pu.OrderDate
WHERE p.ID = *thePersonYouWant*

otherwise...

SELECT TOP 1 *
FROM Person p
INNER JOIN
(
 SELECT TOP 1 pu.ID
    FROM Purchases pu
       ON pu.PersonID = p.Id
 ORDER BY pu.OrderDate
) sq

I think! I haven't got access to a SQL box right now to test it on.

Without knowing your structure at all, or your dbms, you would order the results descending by the purchase date/time, and return only the first joined record.

Try TOP 1 With an order by desc on date. Ex:

CREATE TABLE #One
(
    id int
)

CREATE TABLE #Many
(
    id int,
    [date] date,
    value int
)

INSERT INTO #One (id)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

INSERT INTO #Many (id, [date], value)
SELECT 1, GETDATE(), 1 UNION ALL
SELECT 1, DATEADD(DD, 1 ,GETDATE()), 3 UNION ALL
SELECT 1, DATEADD(DD, -1 ,GETDATE()), 0

SELECT TOP 1 * 
    FROM #One O 
    JOIN #Many M ON O.id = M.id
    ORDER BY [date] DESC

If you want to select the latest purchase for each person, that would be:

SELECT PE.ID, PE.Name, MAx(PU.pucrhaseDate) FROM Persons AS PE JOIN PURCHASE as PU ON PE.ID = PU.Person_ID

If you want to have all persons also those who have no purchases, you need to use LEFT JOIN.

I think you need one more table called Items for example. The PERSONS table would uniquely define each person and all their attributes, while the ITEMS table would uniquely define each items and their attributes.

Assume the following:

Persons         |Purchases                 |Items
PerID PerName   |PurID PurDt  PerID ItemID |ItemID ItemDesc ICost
101   Joe Smith |201   101107 101   301    |301    Laptop   500
                |202   101107 101   302    |302    Desktop  699
102   Jane Doe  |203   101108 102   303    |303    iPod     199
103   Jason Tut |204   101109 101   304    |304    iPad     499
                |205   101109 101   305    |305    Printer   99

One Person Parent may tie to none, one or many Purchase Child. One Item Parent may tie to none, one or many Purchase Child. One or more Purchases Children will tie to one Person Parent, and one Item Parent.

select per.PerName   as Name
     , pur.PurDt     as Date
     , itm.ItemDesc  as Item
     , itm.ICost     as Cost
  from Persons   per
     , Purchases pur
     , Items     itm
 where pur.PerID   = per.PerID          -- For that Person
   and pur.ItemID  = itm.ItemID         -- and that Item
   and pur.PurDt   =                    -- and the purchase date is
     ( Select max(lst.PurDt)            -- the last date
         from Purchases  lst            -- purchases
        where lst.PerID  = per.PerID )  -- for that person

This should return:

Name         Date    Item    Cost
Joe Smith    101109  Ipad     499
Joe Smith    101109  Printer   99
Jane Doe     101108  iPod     199