It is possible to perform a query on the same column multiple times to get different sets of results. Due to the types of joins needed this can only be done with v15R4 and above. In v15R4 extended support of outer joins were added which is needed in the query used to query a single column more than one time.

Below is an example of some tables and data.

TABLES:


[Persons] RECORDS:


[Event] RECORDS:


To query the [Event] table multiple times on the EventCompleted field the following query will be used to dynamically generated a liftbox:

Begin SQL    SELECT    Person.ID AS ID,    Person.Name AS Name,    Event1.EventCompleted AS Bench,    Event2.EventCompleted AS Squat,    Event3.EventCompleted AS Deadlift    FROM    Person    LEFT JOIN    Event AS Event1    ON    Event1.PersonID=Person.ID    AND    Event1.EventName='Bench'    LEFT JOIN    Event AS Event2    ON    Event2.PersonID=Person.ID    AND    Event2.EventName='Squat'    LEFT JOIN    Event AS Event3    ON    Event3.PersonID=Person.ID    AND    Event3.EventName='Deadlift'    INTO <<vlistbox>> End SQL

Running the command above on a listbox assigned to the vlistbox process variable will result in the following:


As seen the single query above allows the [Event]EventCompleted column to be accessed multiple times for a single row of data.

More on the Support of Joins with v15R4:
http://livedoc.4d.com/4D-v15-R4-Upgrade-15-R4/SQL/Extended-support-of-outer-joins.300-2800718.en.html