temp table and join

[crjo]

Both of these work to a certain degree, but neither is returning the desired result.

CREATE TEMPORARY TABLE `tmplist`
SELECT medicalrecords.resident_id, medicalrecords.medicalrecorddate
FROM medicalrecords
GROUP BY medicalrecords.resident_ID;
SELECT residents.resident_id, residents.lastname FROM `residents`
LEFT JOIN tmplist AS t1 ON (residents.resident_id=t1.Resident_ID)
ORDER BY residents.lastname
DROP TABLE tmplist

Returns ONLY resident_id, lastname from `residents`.

CREATE TEMPORARY TABLE `tmplist`
SELECT medicalrecords.resident_id, medicalrecords.medicalrecorddate
FROM medicalrecords
GROUP BY medicalrecords.resident_ID;
SELECT * FROM `residents`
LEFT JOIN tmplist AS t1 ON (residents.resident_id=t1.Resident_ID)
ORDER BY residents.lastname
DROP TABLE tmplist

Returns all from `residents` (120 columns) AND resident_id, medicalrecorddate from `tmplist`.

What I want returned are 4 colums: resident_id, lastname from `residents` and resident_id, medicalrecorddate from `tmplist`.

Any help is greatly appreciated.

 

[ericbrunson]

SELECT medicalrecords.resident_id, medicalrecords.medicalrecorddate
FROM medicalrecords
GROUP BY medicalrecords.resident_ID;

I'm suprised you don't get an error on that statement. You must reference all fields in the select clause in the group by clause unless you're using some aggregate function, like "max()".

 

[r937]

i'm not surprised there was no error message

you have witnessed another example of the mysql misfeature of allowing "hidden" fields in the GROUP BY

see 12.9.3. GROUP BY with Hidden Fields

crjo, why the temp table? this looks like a simple join, no temp table required

oh, and about the GROUP BY... which medicalrecorddate do you want for each resident_id?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ericbrunson]

Interesting, I wasn't aware of that. And I agree it is somewhat of a misfeature.

It's sad that so many developers get their first RDBMS experience with MySQL and they've taken such liberties with the ANSI standard.