Join 2 queries (mssql)

Grafana MSSQL
1

I work with 2 separates servers with MSSQL.
I need join 2 results queries in a bar gauge visualization.

QUERY A

image
image1408Ă—365 16.4 KB

QUERY B

image
image419Ă—316 15.3 KB

I need a result table with this columns
ID_ORIGEN
ORIGEN
CANTIDAD

I cant understand how it is so dificult.

Help pls, thanks in advances.

  • What Grafana version and what operating system are you using?
    v 11.4 - Docker
  • What are you trying to achieve?
    Join 2 queries from 2 diferents mssql servers
  • How are you trying to achieve it?
    Whatever
  • What happened?
    I cant
  • What did you expect to happen?
    Work
  • Can you copy/paste the configuration(s) that you are having problems with?
    N/A
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    N/A
  • Did you follow any online instructions? If so, what is the URL?
    A lot
2

Hi,
It shouldn’t be difficult. Are you using Join by field transformation? The only tweak is what you want to present - inner or outer join (work just like in databases).

image
image1359Ă—551 22.1 KB

image
image1359Ă—551 29.7 KB

3

Thank you very much for your reply.
I swear that I have done and redid what you sent me a thousand times and it does not work for me.

image
image1431Ă—914 49 KB

I managed to make it work with a “merge series/tables” + “group by” + “organize fields by name”.

I really appreciate you replying.

A big hug and have a great end of the year.

4

I think there is one difference - what’s the type of your ID_ORIGEN field? As you can see in my screen I picked the one with (base field name) but my fields are both String Types. As I can see in your query, it’s a string type in the table but Grafana renders the join transformation as it was number - maybe it can’t join on fields with same name but different types?

1 Like
5

For performance reasons I ended up asking them to create the destination table in the same database (it wasn’t very big and it also greatly improves the performance of the query).

But now that I read your answer I think it is as you say, ID_ORIGEN is an extraction of a field that contains a JSON and that through a view it was converted into a varchar type column.

image
image810Ă—113 9.58 KB

And the other query did have the ID_ORIGEN as an integer.

image

Surely the problem was there although I remember having used the CAST(ID_ORIGEN AS INT) and anyway it did the same thing to me.

Anyway you made me understand how some things work and I value it very much.

Hugs.-

1 Like