I have 4 tables:

I need to get a client's state name from a job OR order. If job is not null get from job, else get from order.

I tried something like this:

LEFT JOIN job.client jc
LEFT JOIN order.client oc
LEFT JOIN COALESCE(jc.state, oc.state) clientState

but I get an unexpected token: COALESCE exception.

I also tried:

LEFT JOIN job.client jc
LEFT JOIN order.client oc
LEFT JOIN CASE WHEN job IS NOT NULL THEN jc.state ELSE oc.state END clientState

but I get an unexpected token: CASE exception.

Any idea on how to solve this? Should I try multiple JOINS with state table (jc.state and oc.state) and use the CASE in the projection? Isn't there an easier way?

Thanks in advance

Extra info:

This query could be solved like the example below. My main question is if there is a better way of doing this:

SELECT CASE WHEN jcClientState IS NOT NULL THEN jcClientState.code ELSE ocClientState.code END 
FROM job job
LEFT JOIN anotherTable anotherTable
LEFT JOIN job.client jc
LEFT JOIN anotherTable.order oc
LEFT JOIN jc.state jcClientState 
LEFT JOIN oc.state ocClientState

CodePudding user response：

Assuming HQL supports coalesce (appears as such when doing a quick search), then you can use coalesce like this:

    select coalesce(table1.state, table2.state, 'unknown') as state
    from table1
    left join table 2
      on table2.id = table1.id

The coalesce will grab the first non-null value.