Inner join transform in mixed data source pick only one joined row

Grafana Dashboards
,
1

I am using Mixed data sources
I used two postgres databases
Created two queries
In transform section, used Join by field => Mode as Inner join

but it is returning only one row instead of many-to-many join

Query1 result
user_id, name
1,abc
2,def

Query2 result
order_id,user_id
1,1
2,1

Transform result
user_id,order_id,name
1,2,abc

Expected transform result
user_id,order_id,name
1,1,abc
1,2,abc

Is many-to-many or many-to-one join not possible in grafana ?

Thanks in advance!

2

Hi @mir1198yusuf,

Welcome to the :grafana: community support forums !!

We are excited that you joined our OSS community. Please read about some of the FAQs in the community :slight_smile:

As per the documentation, it should be possible to what you want to achieve. However, I do not see a timestamp column in your provided sample so that might be causing problems?

1 Like
3

Welcome

Why not join these different tables in one query instead of using grafana transformation?

4

There is no timestamp column, I want to inner join query 1 and query2 by user_id

5

Like I said I am using mixed data sources, it means both tables are in different databases, so I cannot join them in same query. thanks

6

so are you using data sources from 2 different servers or 2 different databases on same server?

7

Consider it has two different servers altogether and two different postgres database

8

Same problem here. I need to join 2 tables by one value. However the it picks only one joined row. If in table1 I have 2 values = foo, when joining only 1 value is joined. The other one disappears.

9

Same for me, it should join on all possible values.
Query 1:
user_id, timestamp, value
1, 2023-08-24 18:00:00, 2
1, 2023-08-24 18:01:00, 4
1, 2023-08-24 18:02:00, 3
1, 2023-08-24 18:03:00, 3

Query 2:
user_id, username
1, test

Result:
user_id, timestamp, value, username
1, 2023-08-24 18:00:00, 2, test

Expected result:
user_id, timestamp, value, username
1, 2023-08-24 18:00:00, 2, test
1, 2023-08-24 18:01:00, 4, test
1, 2023-08-24 18:02:00, 3, test
1, 2023-08-24 18:03:00, 3, test

10

Can you try the merge transformation instead of the join?

11

I’m facing the same issue. currently I’m able to merge data from different data sources. I’m trying to effectively do something similar to a SQL join where the results are matched on a particular field and if there’s multiple records each field gets a copy of the matching data.

example on join on name
Data source A
name , phone
elias , 333-333-3333
roger, 444-444-4444
peter, 555-555-5555
danny, 666-666-6666

Data source B
name, total sales
elias, 9.99
elias, 10.50
peter, 33.50
roger, 4.49

expected results on inner join

elias, 9.99, 333-333-3333
elias, 10.50, 333-333-3333
peter, 33.50, 555-555-5555
roger, 4.49, 444-444-4444

expected results on outter join
elias, 9.99, 333-333-3333
elias, 10.50, 333-333-3333
peter, 33.50, 555-555-5555
roger, 4.49, 444-444-4444
danny, null, 666-666-6666