View Single Post
xstep

 
xstep's Avatar
 
Member Since: Jun 25, 2005
Location: On the road
Posts: 3,231
xstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to all
Mac Specs: 2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid

xstep is offline
Here is one idea.

SELECT NAME FROM TABLEA AS A1
INNER JOIN TABLEB AS B1 ON TABLEA.ID=B1.ID
AND B1.STATUS='Y'
LEFT OUTER JOIN TABLEB AS B2 ON B1.ID=B2.ID
AND B2.STATUS='M'
WHERE B2.STATUS IS NULL
GROUP BY NAME


Before the where clause, the query is effectively returning the following record complex.

Code:
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.
QUOTE Thanks