CODEWITHSUNDEEP
sqlpostgresql
Kevin
Kevin

Reputation: 6292

select different columns based on outer join

I have two tables tableA and tableB, which have the same structure:

rowid: big int
name:character variying
enabled: boolean

Now I did an outer join on these two tables

select tableA.rowid, tableA.name, tableB.enabled
from tableA 
left outer join
tableB
on tableA.rowid = tableB.rowid

The tableA contains rows whose enabled column are all true while tableB contains some of the rows in tableA which should be turned off (the enalbed column is false).

If I use the join above, it will set the enabled field of matching rows in the result table as false, but it will not set the remaining rows to true. The enabled column is simply empty (cannot find a matching row in tableB).

my question is that is there any ways I can tell the outer join that I want to use tableA.enabled if there is not a matching row in tableB, otherwise, use tableB.enabled?

Or is there any other better ways to do this?

I use PostgreSQL for this. Idea of any other SQL types are also welcome.

Many thanks

Upvotes: 0

Views: 139

Answers (2)

XN16
XN16

Reputation: 5879

select tableA.rowid, tableA.name, ISNULL(tableB.enabled, tableA.enabled) as Enabled
from tableA 
left outer join
tableB
on tableA.rowid = tableB.rowid

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838666

You are looking for COALESCE:

SELECT
    tableA.rowid,
    tableA.name,
    COALESCE(tableB.enabled, tableA.enabled) AS enabled
FROM tableA 
LEFT OUTER JOIN tableB
ON tableA.rowid = tableB.rowid

Upvotes: 4

Related Questions