JOIN of SELECT not working 1

[OsakaWebbie]

I have a table called contact:

`ContactID` int(11) unsigned NOT NULL auto_increment,
`PersonID` int(11) unsigned NOT NULL default '0',
`ContactTypeID` int(11) unsigned NOT NULL default '0',
`ContactDate` date NOT NULL default '0000-00-00',
`Description` text

As you can probably imagine, it relates to a table called person via PersonID.

I'm trying to do a pretty complex query that isn't working, and I've boiled the problem down to this part of it.

I can query thus, as a test using just one person:

SELECT c2.PersonID,MAX(c2.ContactDate) AS Completed FROM contact c2 WHERE c2.ContactTypeID IN (9,16,34,35,36,37,48,58,59) AND c2.PersonID=618

PersonID  Completed
618       2012-01-17

But when I try to do a join like this:

SELECT p.PersonID, x.Completed FROM person p
LEFT JOIN (SELECT c2.PersonID,MAX(c2.ContactDate) AS Completed FROM contact c2 WHERE c2.ContactTypeID IN (9,16,34,35,36,37,48,58,59)) x
ON x.PersonID=p.PersonID
WHERE p.PersonID=618

x.Completed comes back as NULL. Can anyone see why?

 

[r937]

whenever you use an aggregate function like MAX, if there are other non-aggregate columns in the SELECT clause, like this --

SELECT c2.PersonID,MAX(c2.ContactDate) ...

then you must use a GROUP BY clause


enough of a hint?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[OsakaWebbie]

Yeah, that was enough of a hint. I was thrown off the scent by the fact that the SELECT by itself worked in spite of the lack of GROUP BY. (Only inside the JOIN did it fail.) And like I said, this was just part of a much larger query, and I was getting dizzy looking at it... Thanks!