Outsystems Advanced SQL

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.

SQL Query – Goal 1: Retrieve which options each machine have activated. – DONE
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

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'

Advertisement

Answer