Sub Query?

[djorgensen]

I have two tables, one called members and one called visits.

the member table has fields like MemberID and the visits has fields VisitID MemberID and VisitDate

on a visit an entry is made in the visits table either the memberID field and the current date


What im trying to do is select everything from the Member table and the last visit from the visits table.

can anyone help?
thanks

[lenakarthik@yah]

it will help you,

select * from member where memberid = (select memberid from visit where visitid = max(visitid))

Regards
- Karthik

[djorgensen]

That doesnt seem to work, i just get back "An aggregate may not appear in a where clause unless its in a subquery contained in a having clause...."

thanks for the quick reply though

[sdives]

The querys below should return the latest visit for each member that has a row in the visit table. I couldn't make it work in a subquery but I did make it work in two queries by creating a temporary table with the date and memberid then pulling the member info. I assume that the memberid in the members table is unique.


SELECT MemberId, max(VisitDate)
INTO #temptbl
FROM Visit
GROUP BY MemberId

SELECT m.*, t.VisitDate
FROM Members m
JOIN #temptbl t ON m.MemberId=t.MemberId

[Rawhide]

Select *
From members As m
Inner Join visits As v On m.memberid = v.memberid
Where v.visitid = (Select max(visitid) From visits where memberid = m.memberid)


If you want to include entries where there are no visits logged, change the inner join to a left join.