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

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!

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

Welcome

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

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

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

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

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

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.

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

Can you try the merge transformation instead of the join?