Hi,

I have the following in Staff table:

001  Jackson, Paul
002  Henderson, Janette
003  Brown, Heidi

I have a sub table called Positions:

001   A  Director
001   B  Manager
002   B  Manager
003   C  Worker

In my resultset I only want 1 record per employee, but where the person is a manager I want the flag to be True, otherwise false, so:

Jackson, Paul         True
Henderson, Janet    True
Brown Heidi            False

Am using V3.3.0, is this possible in a single SQL statement?  I have tried multi-statements using memory tables which works fine but I keep getting a message indicating access is denied to the memory table on subsequent attempts to access it.

TIA
Al

Alex
> I have the following in Staff table:
>
> 001  Jackson, Paul
> 002  Henderson, Janette
> 003  Brown, Heidi
>
> I have a sub table called Positions:
>
> 001   A  Director
> 001   B  Manager
> 002   B  Manager
> 003   C  Worker
>
> In my resultset I only want 1 record per employee, but where the person is a manager I want the flag to be True,
otherwise false, so:
>
> Jackson, Paul         True
> Henderson, Janet    True
> Brown Heidi            False
>
> Am using V3.3.0, is this possible in a single SQL statement?  I have tried multi-statements using memory tables which
works fine but I keep getting a message indicating access is denied to the memory table on subsequent attempts to access
it.

How about a union like

SELECT Name,true AS IsManager FROM Staff WHERE Id IN (SELECT Id FROM Positions WHERE Position='Manager')
UNION
SELECT Name,false AS IsManager FROM Staff WHERE Id IN (SELECT Id FROM Positions WHERE Position<>'Manager') AND
Id NOT IN (SELECT Id FROM Positions WHERE Position='Manager')

John

<Alex Vastich> wrote in message
news:E1DCC0AF-D47A-46D6-A12A-0FBEB2552A5A@news.elevatesoft.com...
> Hi,
>
> I have the following in Staff table:
>
> 001  Jackson, Paul
> 002  Henderson, Janette
> 003  Brown, Heidi
>
> I have a sub table called Positions:
>
> 001   A  Director
> 001   B  Manager
> 002   B  Manager
> 003   C  Worker
>
> In my resultset I only want 1 record per employee, but where the person is
> a manager I want the flag to be True, otherwise false, so:
>
> Jackson, Paul         True
> Henderson, Janet    True
> Brown Heidi            False
>
> Am using V3.3.0, is this possible in a single SQL statement?  I have tried
> multi-statements using memory tables which works fine but I keep getting a
> message indicating access is denied to the memory table on subsequent
> attempts to access it.
>

Assuming there are no duplicate manager rows with the same employee number,
the following should do it

SELECT S.EMPLOYEE_NUMBER, S.NAME, IF(P.JOB = 'Manager', TRUE, FALSE) AS
IS_MANAGER
FROM STAFF S
LEFT OUTER JOIN POSITIONS P ON (S.EMPLOYEE_NUMBER = P.EMPLOYEE_NUMBER) AND
(P.JOB = 'Manager')

Robert