Query on "one-to-many" table

Query on "one-to-many" table

  
I'm having a little trouble with SQL...

I have this diagram




and the GUEST table looks like this at a certain point 



Knowing the MeetingID how can I get the a CONTACT's name ?

 For instance, for MeetingId 1 in GUEST table I want to get the Name of the contacts with ContactId  equal to 1 and 3.

Thanks.


Hi João,

From your description it looks like all you have to do is do a Simple Query where you join the three tables through the existing foreign keys.

The simple query would look something like the one below.



Regards,

Daniel Lourenço
OutSystems
Hi João,

If you are working with 5.1 then just drag the guest entity to the action flow and open it. Then drag the contact entity to inside the simple query. If you want to know the title of the meeting too, just drag that entity too. The Service Studio will manage the relations between them and you can have access to the attributes you need.

See the video in attach if needed.

RNA

EDIT (Daniel beat me in submit button)
I see... I was trying using an 'advanced query' but my problem is I want to assign the query output to a variable of type 'Record List' whose definition is CONTACT.

So now I get this error The same 'Record' data type required. Expected 'CONTACT' instead of 'CONTACT, GUEST, MEETING'.
 
The same 'Record' data type required. Expected 'CONTACT' instead of 'CONTACT, GUEST, MEETING'.
 
The same 'Record' data type required. Expected 'CONTACT' instead of 'CONTACT, GUEST, MEETING'.
 
The same 'Record' data type required. Expected 'CONTACT' instead of 'CONTACT, GUEST, MEETING'.
You can do the advanced query or add the entities missing in the assign variable.

If you can, use always a simple query. Its easier, simple to change and the platform optimize the query.

Cheers,
RNA
Hi João,

An easy way to convert that simple query you have into an advanced query and use your "Contact" record list output is to:
- test the simple query
- go to the "SQL" tab and copy the generated SQL
- add the advanced query pasting the SQL from the simple query.
- Add the "contact" as the output structure
- In the SQL delete all the fields that don't belong to the "contact" entity.
- Add the input parameters you need adjusting the SQL to them.

This is the process I usually use when creating advanced queries, I automatically guarantee I don't end up with wrong entity/field name and get all the "hard" work done for me by the platform. :)

Hope it helps,
Hermínio Mira