Select records where relative other table is null

daniele.dagostino

Hi there,

I have two tables: messages and users_messages where this last one has a reference to messages by its id.

I'm a java developer and normally I would do

select * from messages m
left join users_messages um on um.message_id = m.id
where um.id = null

My goal is to select all the messages that hasn't a reference to users_messages.

I already tried to do something like this below

Unfortunately It didn't work.

How can I do this in Xano?

pete letkeman

You may have to do something like

'db:um.id' =

I don't know the exact rules, but I've experienced that sometimes blank/empty as shown above works when null does not.

Also, make sure that you are using null correctly, sometimes if you simply time in null Xano treats it as a string value.

daniele.dagostino

Thanks for the tip mate!

I'll try sooner! (I already did understand the difference between null as string and not)

Michael Udinski

@daniele.dagostino the primary id field cannot be null. The id field is locked down to preserve data integrity. You won't find any records where the id = null.

daniele.dagostino

So that, I think the only way do get my solution is to make an aggregation, where I may count the records in the relative table, isn't it?

Michael Udinski

That could be one option, another one I thought of I've detailed in this loom video:

https://www.loom.com/share/80ca214b9e374a9982ea258a19218792