Select from multiple rows based on column value

Question

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

Edit your question and add 1) table structure; 2) sample data; 3) what you've already tried — WillardSolutions
– WillardSolutions, Commented Mar 22, 2019 at 17:13
If you have a 1 to 1 relationship you should not be getting 2 rows or your code is not correct. You need more details, like tables, data examples, desired results. — Brad
– Brad, Commented Mar 22, 2019 at 17:15

Gordon Linoff · Accepted Answer · 2019-03-22 17:16:10Z

1

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>);

answered Mar 22, 2019 at 17:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steven F Over a year ago

Thanks, I was previously importing both sources into one table through a procedure but I'll be putting the two sources in separate tables from now on. This should do the trick.

elizabk · Accepted Answer · 2019-03-22 17:17:59Z

1

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.

answered Mar 22, 2019 at 17:17

elizabk

4802 silver badges11 bronze badges

Comments

MaxPuissant · Accepted Answer · 2019-03-22 17:20:13Z

1

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 ?

answered Mar 22, 2019 at 17:20

MaxPuissant

314 bronze badges

Collectives™ on Stack Overflow

Select from multiple rows based on column value

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related