CODEWITHSUNDEEP
sqlsql-server
Steven F
Steven F

Reputation: 115

Select from multiple rows based on column value

I've got a table with rows of info on various units. This info is sourced from two locations, A and B. Source A contains info from every unit in the table, while source B contains more accurate info but only for some of the units.

How would I go about selecting one row for each unit, but prioritizing the selection of info from source B (when info is available) over source A, thus generating just one row for the unit with data from B? Right now if a unit is in both sources, it will come up as two different rows in my query. I've tried using a case when statement in the where clause and IF statements in the select statement.

I feel like there's a very simple solution to this but for some reason I'm struggling to figure this out. Thanks in advance.

Table Structure: UnitKey(PK) UnitID Hours DataSource

Upvotes: 2

Views: 1768

Answers (3)

MaxPuissant
MaxPuissant

Reputation: 31

SELECT
    table_A.Unit,
    ISNULL(Table_B.Value1, table_A.Value1) AS [Value1],
    ISNULL(Table_B.Value2, table_A.Value2) AS [Value2],
    etc ..
FROM table_A
LEFT OUTER JOIN Table_B ON Table_B.Unit = Table_A.Unit

How about that ?

Upvotes: 1

elizabk
elizabk

Reputation: 480

Join the 2 tables, presumably A LEFT JOIN B. You can use a CASE statement in your SELECT to check if the information is available in table B, ELSE take from table A.

If you write a more detailed question I can write a more detailed answer.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271121

You can use union all:

select b.*
from b
union all
select a.*
from a
where not exists (select 1 from b where <matching conditions here>);

Upvotes: 1

Related Questions