Outsystems Advanced SQL

Question

I will start by introducing my business logic.

I have an entity called Machines. To each Machine I will assign up to 50 Options. On this query I used PIVOT so I can retrieve only one line per machine, with all the options (Columns) with value 0 or 1.

Note: My Output structure is ready for 50 Options.

1. SQL Query - Goal 1: Retrieve which options each machine have activated. - DONE

2. SQL Query - Goal 2: Filter machines that have X and Y Option activated. -

My Entities Diagram:

My Query:

SELECT MachineID, MachineSN ,@StringIn


FROM (    
    SELECT {Machine}.[Id] as MachineID,
            {Options}.[Name] as OptionName,
            {MachineOption}.[OptionActive] as IsActive,
            {Machine}.[SNumber] as MachineSN

         
    FROM {MachineOption}
    INNER JOIN {Options} ON {Options}.[Id] = {MachineOption}.[OptionId]
    INNER JOIN {Machine} ON {Machine}.[Id] = {MachineOption}.[MachineId]     
    GROUP BY {Options}.[Name], {MachineOption}.[OptionActive], {Machine}.[SNumber], {Machine}.[Id]
    
        
    
) R
 Pivot (max(IsActive) for OptionName in (@StringIn)) as Columns Order By 1

Result of the actual query:

I need some help with this query. I already tried to filter in the inside query but I always get one only with only one option.

Thanks in advance.

Answer 1

You can search the table twice (one per option) and then join both searches. For example, you can do:

select distinct machineid
from machineoption x
join machineoption y on y.machineid = x.machineid and y.optionid = 'Y'
where x.optionid = 'X'