How can make a join condition with a structure

How can make a join condition with a structure

Hi all,
I have a complex query that i need in a project, that i splitted in 2 simpler queries to make things easier, being one them an advanced query and the other a normal query.
The idea was to make the hard part of the complex query using the advanced query block and then join the result with the normal query to obtain the result i wanted.
While this looks fine in theory, it didn't work when i tried to do it in the Service Studio. The problem is that, when i try to make a join condition of an entity with a structure the system doesn't detect that is a JOIN and puts it like a standard condition, and then the query fails.

Is there a solution to this, other than building the entire complex query in an advanced query block?

Thanks in advance,
Luís Pedro
Hi Luís,

Lets start from the SQL, a Join is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. (you can see more details here)

In an advanced query what you can do is to define an output structure combining one ore more entities and one or more structures, but the results are based on a SQL instruction that your database server must understand. For more details on this subject please see the webinar "Elaborating the Web Pages Logic" in the developer course one @ the online academy.

About your problem, maybe you can share the espace with the community to somoene can take a look and help you more on that.

Hi Ricardo,

It's a bit hard to put an espace to show my problem, as it wouldn't make sense to share the whole project, wich is kind of a big one! :) Nonetheless, i'll try to post it when i can.

In my Ticket Management app i have an entity to store the users (Utilizador), another one to store the groups (Grupos) and another one to store the users that belong to a group (GrupoUtilizador), besides the Ticket entity to store the tickets (roughly explained). I also have some users that have a special property, that they can see the tickets belonging to the users of the groups that the first user belongs (sorry, but i think i can't explain it any other way, without a phone at least!!!). The queries mentioned above are the queries to obtain those tickets.

I took some print-screens to try to explain a bit more in detail:

This is the first query, in SQL, gets which user's tickets should be returned, as you can see it gets the user id's and names,


On the second query, I'm trying to use the structure returned earlier and join it with the tickets entity, so i can get the tickets from those users, as you can check in the last condition,

So, my problem is that the query editor doesn't create an entity JOIN condition as i thought it would, it just treats the structure as an input variable and not as a special entity.

Sorry for the BIG post, and i'll try to create a small espace for you to mess around with.

Luis Pedro
Hi Luis,

Well, let me chime in for a bit. Let's start by thinking that both Advanced Queries and Simple Queries in the Agile Platform are exactly the same as normal, hand-written queries to the database. That being said, I would start by raising the question of how would you do this in pure SQL - i.e. using two queries to achieve the result you want.

As I see it, you have at least two options:

1 - Do the simple query, and then have an Action/Function that retrieves the data you want for an individual user. When you iterate the list - be that in a Table Records, or in an Action's logic, then you retrieve the data for each user.

2 - Do it like you would do it in SQL. Either a single Advanced Query with an IN statement

pseudo-SQL: (Get Ticket Data Where User In (Get User List))

or two queries: the simple one, and then generate a list of IDs (1, 2, 5, 7, 9, 11, ...) and do an Advanced Query with the IN statement as well.

pseudo-SQL: (Get Ticket Data Where UserId In (User ID List))

I hope these suggestions help. If I understood your problem correctly, these should somehow show you a way for you to tackle that problem.

I hope these help! Let us know how it all goes.


Paulo Tavares
Hi Luís,

I may be looking at it the wrong way ..but that first query looks way overkill for what it's suposed to do.
I understand the need for the distinct since a user can be in multiple groups ..but the "IN"? Why?

Select distinct {Utilizador}.[Id], {Utilizador}.[utilizador]
From {Utilizador}
Inner Join {GrupoUtilizador} On {GrupoUtilizador}.[idUtilizador] = {Utilizador}.[id]
Where {GrupoUtilizador}.[Visibilidade] = 1

Did I miss something? This can easily be done with a Simple Query (apart from the distinct), or even added as 2 conditions on your second Query.
Also why are you filtering "{GrupoUtilizador}.[idUtilizador] = @IdUtilizador" ..that would make the query only return 0 or 1 id's making the "GetUtilizadoresVisiveis" name unfit.

João Rosado
Hi there

Here are my .02

its is usually never a good idea to separate querys and process them in code, nothing beats the power of a sql server, so why re invent the wheel

I'm not sure what you are trying to do with your query, but I think with some tweeking my sugestion might work.

try something like this:

Select tickect.*,u.*
From {Utilizador}
Inner Join {GrupoUtilizador} On {GrupoUtilizador}.[idUtilizador] = {Utilizador}.[id]
                            and {GrupoUtilizador}.[Visibilidade] = 1
inner join ticket on ticket.idutilizador = {Utilizador}.[Id]
                  and (ticket.estadoticketid = @triagem or ticket.estadoticketid= @outro)
inner join     {Utilizador} U on ticket.idtecnoco =

A few tips:
where something in(query) is allways a bad idea it will force the server to do a nested loop, they can allways be replaced by an inner join
for MSSQL there is a big diference between WHERE clauses and ON clauses, where clauses are applied after the join, while on clause are during the join

Hi Paulo,

I totally agree with your post. For now i'm using your first solution, but i'm probably moving to the second one, with only one Advanced Query to return the results i need. The first option has a problem that I overlooked for a while: the final list is somewhat a "concatenation" of lists, so when you try to order the full list it doesn't work, as it tries to order the sub lists before the "concatenation". I was trying not to use advanced queries because they are harder to manage and maintain.

By the way, I already understood why i can't use structures as normal entities with normal joins. I found out that structures only add variables to fill-in after queries are made (I had some misconception about this). Nonetheless, another approach that would solve my problem and a bunch of other alike is something i found out as an idea on Wisdom of Crowds. The main concept is about being able to use the result of a query as an entity on another query, making possible to make nested SQL queries without Advanced Queries involved.

Here is the link for the idea, if you like it, punch the like button!!! -

Thanks for all the patience and help!
Luis Pedro
Hi Luis,

Well, I'm glad you got it to work. Still, I would heed João's and Rui's advice. João's alternative might be a good one, while Rui knows much more about SQL Server and its performance than I would dare to suggest :)

With that in mind, even if right now you're using my first suggestion, you should consider moving away from it for performance reasons, as Rui stated.

I hope all goes well - and you did well in bringing up the link for the Wisdom of the Crowds. If that's a common thing for people to have to do, then it should definitely be voted for!


Paulo Tavares
Hi Luís, 

just to add more 2 cents...keep in mind that, although it's an old one, but there's an Issue Manager example available to download and use it as example here.

Hi all,

Many thanks for all the help along this post. Although I already have some working solutions, I'm still gonna look a bit further on those more hard-core SQL solutions that Rui and João brought to my atention. I'm a "jack of all trades" sorta guy so i normally don't specialize too much on every subject, but I'm gonna study a bit more on those options because it really seems they're probably the more efficient ones.

Thanks again!!
Luis Pedro

PS: RNA, I did check that Issue Manager example, but i wanted something a bit more sophisticated and adapted to my reality. Maybe one of these days I'll post a light version of the ServiceDesk solution we're using!