Need help with a somewhat complex query...

Need help with a somewhat complex query...

  
I am trying to create a table that shows all of the machines associated with a particular ProductId.  

My tables are:

ProductAplications
  Id
  ProductId (FK)
  ApplicationId (FK)

Applications
  Id
  Name
  Version

ApplicationInstalls
  Id
  ApplicationId (FK)
  MachineId (FK)
  MachineGroupId (FK)
 
MachineGroupMembers
  Id
  MachineId
  MachineGroupId

Using a simple query, I can successfully get a list of all MachineIds that are associated with all Applications that are associated with a particular ProductId.

What I would like to do, is for Applications that are associated with a MachineGroupId instead of a MachineId, return (in addition to the results from the simple query above), all MachineIds from the MachineGroupMembers table where the MachineGroupMembers' MachineGroupId matches the MachineGroupId from the ApplicationInstalls table.

The attatched file shows the simple query that is currently working.

Thx.

Craig

Hi Craig,

Can you provide an OML with your data model ?

Can one record of your table ApplicationInstalls have both MachineId and MachineGroupId filled ? Or each of these records only have either one of these atributes filled ?

Do you want to get duplicate MachineIds? Or you want each MachineId just once?


João Campos
The query in question is on the Product Show web page.

The ApplicationInstalls should have either a MachineId or MachineGroupId, but not both (though I don't know how to code that logic in except by adding a "type" field to the entity and using an If widget on the Product Edit page to control which drop down field is displayed.)
Hi Craig,

I think that you can accomplish that by outer joining ApplicationInstalls both with MachineGroups and Machines.


I've modified your eSpace with a new query. Test it and tell me if its  what you need (you'll have to include your orderby logic again)



João Campos