Member Since: Jun 25, 2005
Location: On the road
Mac Specs: 2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid
12-24-2008, 07:27 PM
Here is one idea.
SELECT NAME FROM TABLEA AS A1
INNER JOIN TABLEB AS B1 ON TABLEA.ID=B1.ID
LEFT OUTER JOIN TABLEB AS B2 ON B1.ID=B2.ID
WHERE B2.STATUS IS NULL
GROUP BY NAME
Before the where clause, the query is effectively returning the following record complex.
A1.NAME B1.STATUS B2.STATUS
------- --------- ------------
Bill Y NULL
Tom Y M
Steve Y NULL
The where clause filters on those NULL values of B2.STATUS thereby excluding the status which is M.
CameraTime - Time lapse photography for novice and advanced users.
When asking questions, post the version of your software. You'll receive better answers.
Please post your results to the thread as it is good feedback.