Strange results in a table

Strange results in a table

I'm trying to create a fairly complex Simple Query and I'm seeing some strange results.  Without getting in to excessive amounts of detail, I have the following tables:  Applications, Machines, MachineGroups, ApplicationInstallations, and MachineGroupMembers.

Applications can be installed on Machines or MachineGroups and those are defined in the ApplicationInstallations table:

ApplicationId (FK)
MachineId (FK)
MachineGroup (FK)

Machines can be members of machine groups and those are defined in the MachineGroupMembers table:

MachineiId (FK)
MachineGroup (FK)

I would like to create a query that will return all applications installed on a particular machine using either the MachineId or any MachineGroupId that machine is a member of.

It was straight forward to get the list of applications defined for a particular machine -- I just dragged the ApplicationInstallations entity onto the Show Machine page.  I then tried to modify the simple query on the Preparation for that table so that it would include Applications installed on MachineGroups.  Those changes either returned nothing or it would return the same result thousands of times.

I tried to walk through each step and see where things were breaking.  The first step was dragging the MachineGroupMembers entity into that query.  I did that without creating any new joins and uploaded it.  When I click on a machine that has only one application installed on it, I see the same application listed three times in the table.  I've double-checked the database table to make sure there aren't duplicate entries and there aren't.  

Why would adding an entity to a query without modifying any of the join statements cause my results to appear in triplicate?


Hello Craig

Maybe I didn't get the entire use case correct, but if your ApplicationInstallations entity has both the MachineId and the MachineGroupId attributes, it already associates the Applicaiton to either the machine or the machinegroup.

If you know either the machine or the machinegroup ID you which to list, then isn't the query below enough? Why do you need the MachineGroupMembers entity?

I had to tweak a little the condition of the MachineId and MachineGroupId, because you might have entries with one of the values empty.

I couldn't identify exactly which query you're refering on the espace, but dragging entities into a simple query, forces the Service Studio to attempt to match the entities through the existent relations, and when you have complex relations with entities both referencing the same foreign entity (like both the ApplicationInstallations and the MachineGroupMembers reference the Machine entity and the MachineGroup entity), sometimes the automativally generated inner joins conditions aren't what you're looking for.

If this isn't what you need, can you identify the query on your espace, by identifying the screen, and the action and the query name.



Miguel Simões João
The query should have been the last one on the Preparation for the Machine Show page.

Maybe it would be better to explain the end result I'm trying to get to.  When I navigate to the Machine Show page from any number of links, the page will put the Machines.Id value for that particular machine into the MachineId.  Given that particular MachineId, I want to grab all of the MachineGroups.Id records from the MachineGroupMembers table that have a matching MachineId.  I then want to query the ApplicationInstalls table and return all of the records that match either the MachineId or any of the MachineGroupIds that were returned from the query to the MachineGroupMembers table.

The simple query to return all ApplicationInstalls with the MachineId is easy.  I've tried testing a query that would find the MachineGroupIds from the MachineGroupMembers table and then return all ApplicationInstalls with matching MachineGroupIds.  That query is:

I bring up the Machine Show page for a machine that is a member of one group.  The group it is a member of has one record in the ApplicationInstalls table.  And yet when I look at that table, the application from the ApplicationInstalls table appears 658 times.

I totally don't get what I'm doing wrong here.

Thanks for your help!

Hi Craig,
If I get it rigth there are some issues with your query.
I've made some changes please check and try.

I hope this helps.
Carlos Rocha

EDIT: please change the join between the MchineGroups and MachineGroupMembers to be MachineGroups With or without machine group members.