SQL Join help

[rh71]

(simplified to explain easier)
TABLE A: name, age
TABLE B: name, verifiedby, verifieddate

1 name can have multiple ages (recorded in TABLE A) and each time they are verified by a person and timestamped (hence the records in TABLE B).

My Resultset after join (to show you what data is being pulled):
SELECT a.name, a.age, b.verifiedby, b.verifieddate
FROM A LEFT JOIN B ON a.name = b.name

NAME - AGE - VERIFIEDBY - VERIFIEDDATE
Joe - 21 - Abraham - 1/14/05
Joe - 24 - Abraham - 5/15/08
Joe - 24 - Bob - 5/16/08
Carl - 12 - Bob - 3/11/07
Carl - 13 - Bob - 5/16/08
Carl - 13 - Abraham - 5/14/08


DESIRED Resultset (only the latest [max] entries for each name, Joe & Carl):

NAME - AGE - VERIFIEDBY - VERIFIEDDATE
Joe - 24 - Bob - 5/16/08
Carl - 13 - Bob - 5/16/08

What is the SQL for this DESIRED resultset? I tried using max(b.verifieddate) with group by but it doesn't work.
SELECT a.name, a.age, b.verifiedby, max(b.verifieddate)
FROM A LEFT JOIN B ON a.name = b.name
GROUP BY a.age, a.name, b.verifiedby

Instead, it just shows me all of them again, like the top resultset.

 

[lozina]

what about something like:

select a.name, a.age, b.verifiedby, b.verifieddate
from
a, b,
(select a.name, max(b.verifieddate)
from a left join b on a.name = b.name
group by a.name) x
where a.name = x.name
and b.name = a.name
and b.verifieddate = x.verifieddate


sorry i cant confirm syntax on that right now but basically your group by doesnt work because you are grouping by all the fields so you are not getting any more unique results then just selected them all like your first select. what you need to do is make a inner select with a group by which works on the fields you want to be unique, then the outer select joins against it and the other tables again to get the additional data

edit: actually you need one more clause in the where

 

[mcmilljb]

You're joining the entire tables together. You need a WHERE clause to determine which rows to joined together.

Try adding this:

SELECT a.name, a.age, b.verifiedby, b.verifieddate
FROM A LEFT JOIN B ON a.name = b.name
WHERE b.verifieddate = 5/16/08;

That should do it because you join the entire tables together and then remove all the entries without that date.

To do it faster and use less memory(by not having both tables combined and being held in memory), you could use a nested SELECT statement by first getting the entries from table B and then joining them with table A. For small simple data like this, there is no real need for the extra work to do it.

 

[KLin]

It's not really going to work since there's going to be more than one record of the same name with different ages in Table A. How do you distinguish between the records and who verified which record? There needs to be a different relationship between the 2 tables than just Name that uniquely ID's the record in A, and records that UniqueID in B as part of the record.

 

[reverend boltron]

What is the datatype for date?

 

[JACKDRUID]

TABLE A: missing TableA_ID
TABLE B: missing TableB_ID, TableA_ID

add these ids, join by TableA_ID see if it works.

 

[reverend boltron]

Here is the best I can do for you.

select b.name, a.age, b.verifiedby, max(b.verifieddate) from a, b where a.name=b.name group by b.name

There are ambiguous cases in there, which is why it is returning 12 instead of 13 for Carl.

You should use primary keys.

Maybe rearrange the tables like..
Table A
a.a_id =>Primary key
a.a_name
a.a_age

Table B
b.a_id
b.b_verifiedby
b.b_verifieddate