AnsweredAssumed Answered

Execute SQL join tables

Question asked by glorifindal on Nov 19, 2015
Latest reply on Nov 20, 2015 by glorifindal

Hi there,

been trying to use JOIN in some ExecuteSQL statememts ... and failing miserably.

 

The table structure looks so ...

Project

    |

Service (contains the Project ID)

    |

ServiceParts (many to many join table - does not contain the Project ID)

    |

Parts (contains the Project ID)

 

I am trying to use ExecuteSQL in the following scenario:

I want to get all the Parts that are related to Service that are also related to the Project.

 

SELECT id_part from ServiceParts WHERE id_service = $_service.id ;

then

SELECT id from Parts WHERE id_part is in $_result (previous query) AND also WHERE id_project = $$_project.id

 

In FM I would most likely use a CustomList function - but have been ORDERED by the boss to use the ExecuteSQL function from now on.

My head hurts now.

 

Can anyone give me an example of this sort of JOIN usage ?

I would be most thankful.

 

Kindest regards

 

G

Outcomes