One-to-many with one-to-one "preview"

(Note: I am using PHP and MySQL 4.1)

Please bear with me as I try to explain this as simply as possible. I have two tables, A and B, with a one-to-many relationship from A to B. AID is the primary key of A, BID is the primary key of B, and AID in table B is a foreign key that references A:

TABLE A:

AID(PK) ANAME
--- -----
1 FRED
2 JOHN
3 MARY

TABLE B:

BID(PK) AID(FK) BDATE BTEXT
--- --- ---- -----
1 2 2004-12-07 9999
2 3 2004-06-30 8888
3 1 2004-11-15 7777
4 2 2004-12-01 6666
5 1 2004-04-10 5555
6 3 2004-08-19 4444
7 1 2004-01-30 3333

I want to develop a query that will show all records in A, and matched up against ONLY ONE record of B. More specifically, the B record displayed MUST be the record from the matching "group" that has the most recent BDATE. For example: when the record with AID=1 is displayed, there are three records from B that have AID=1. The record that has the most recent BDATE is BID=1, so that record should be paired up with the record from A. The outputted result would look something like this:

AID ANAME BID AID BDATE BTEXT
--- ----- --- --- ----- -----
1 FRED 1 1 2004-12-07 9999

At this point, the other two records in B that have AID=1 should just be ignored. I only want records from A listed ONE TIME.

Thus, the complete join for both of these tables should end up looking like this:

DESIRED RESULT:

AID ANAME BID BTEXT
--- ----- --- -----
1 FRED 3 7777
2 JOHN 1 9999
3 MARY 6 4444

I pulled this off with a correlated subquery, but the performance was not great. Does anyone know of a more efficient way to achieve these same results?

Thanks in advance!

What's the subquery you're using? I think that's going to be the way to go.

Is this what you're looking for?



You mentioned only one result or something... perhaps GROUP BY b.aid or something?

The problem with GROUP BY is that it won't pull the latest date. From the info above, you'd get:


1 | FRED | 3 | 7777
2 | JOHN | 1 | 9999
3 | MARY | 2 | 8888

How about adding ORDER BY a.aid DESC?

Won't matter. You'll still get the same results, just in reverse order. The problem is that when you group by the id, it's going to grab the first three rows it hits. In this case, that's fine for Fred and John, but Mary's June row will be chosen instead of her August one, so you're getting 8888 instead of 4444.