Left Join on empty set problem

Hello everyone,

I have two different query result here:

  1. The result from mysql LEFT JOIN, which has empty columns,
  2. The result from query-json-with-sql LEFT JOIN, which has non-empty columns only.

version 1: query and result
(Shows both yellow and green, even if green side is empty)


version2: query and result
(Shows only yellow columns becuase green side is empty)


What I want to do is make a result like version1 (mysql) from query-json-with-sql. (To show both yellow and green columns even if green side is empty set)
When green side is not empty, version 1 and 2 shows the same result.

How could I do this? Any helps or hints would be very grateful.

The reason I want to use query-json-with-sql instead of mysql query is that I don't want to specify column names in query statements.
(In the empty set case, I found that the result from mysql shows null on key column)

Thank you in advance!

Hello! Hmm...I'm not quite sure how to do this with query JSON with SQL and will have to do some testing. But, I do know it will work with a regular SQL query. Could you elaborate on why just writing the SQL query isn't enough? :thinking:

Hello jSims, thanks for the reply!

There are two reasons for that.

  1. To reuse the result of individual selection
    For my understanding, I can't use individual query result when I use regular sql query with join.
    The joined set itself became an object in retool (e.g. {{ yellow_green_joined.data }}
    On the contrary, I can use seperated set of data when I decide to join them after retrieval. (e.g. {{yellow.data}} and {{green.data}})
    Then I can reuse {{yellow.data}} somewhere else in my app.
    However, to use two individual sets and one joined set from regular query, I need to query mysql DB for 3 times.
    Since I'm experiencing some delay on the retool app with mysql query, I'm trying to optimize the number of retrievals.

  2. Not to explicitly use column names
    (I think this one could be resolved, but I don't have a solution for now.)
    When I try left join in general query without specify column names,
    query3

I got result like this:

Yellow and green table both have col_seq column.
The result shows col_seq as null, even if yellow table has not null values.
I know I could call one col_seq as other name, but then I need to specify column names.

Thanks,
padam

Hello! Very sorry about the delay. If you're still having trouble could you write to support so that we can take a look into your app and see if we can find a workaround? The only thing I can think of is writing a conditional query that returns an object with the column names and empty values if the join returns null.

Hello, Thanks for revisiting. For my understanding, it requires the column names explicitly to make the object, which is not quite desirable. But since my requirements for this app changed, so I'm not getting bothered by this issue anymore.