Hello Kirk,

I'm sure there is a more succinct solution than this. I only tried it on local data, so you will have to modify the table names to try it:

SELECT ctstatus.statusid,ctStatus.statusdesc,  ;
        ttoc(ctPatStatus.statustime) AS statusTime ;
    FROM ctStatus ;
    LEFT JOIN ctPatStatus ;
        ON ctStatus.statusid = ctPatStatus.statusid ;
    WHERE ctPatstatus.meetingNumber = ?gnMeetID ;
UNION ;
SELECT statusID, statusDesc, "NULL" AS statusTime ;
    FROM ctStatus ;
    WHERE statusID NOT IN ;
        (SELECT statusID FROM ctPatStatus ;
            WHERE meetingNumber = ?gnMeetID)

Off course, "NULL" can be any message you want...

HTH,

steve




>I have two tables. First table is called ctStatus. It contains a list of defined status codes:
>
>

>statusid   statusdesc     isactive
>--------------------------------------------------
> 1         enter room        1
> 2         exit room         1
> 3         arrive            1
> 4         depart            1
>
>

>
>The second table contains status by meetingnumber. The user would click on a status to assign it to a meeting it's called ctPatStatus
>
>

>patstatusid   meetingnumber  statusid  statustime
>---------------------------------------------------------
>1                1             1        02/10/2002 17:00
>1                1             2        02/10/2002 18:00
>
>

>
>What I want to be able to do is create a cursor that I can present to the user that displays all the available events, and beside the events that have a datetime show that, otherwise show a null.
>
>So, based on the above data, the cursor I would would get would look something like:
>
>

>enter room       02/10/2002 17:00
>exit room        02/10/2002 18:00
>arrive           NULL
>depart           NULL
>
>

>
>
>The following this the vfp code I have tried:
>
>

>SELECT dbo.costatus.statusid,dbo.coStatus.statusdesc,
>       dbo.coPatStatus.statustime
>FROM dbo.coStatus LEFT OUTER JOIN
>     dbo.coPatStatus ON dbo.coStatus.statusid = dbo.coPatStatus.statusid
>WHERE coPatStatus.meetingnumber=?gnMeetID
>
>

>
>But I get no results once I put the where clause. If I leave the where cluase off, I all the events and matching ctPatStatus records, but not limited to the ones I need.
>
>Thanks