CODEWITHSUNDEEP
sqlsql-servert-sql
XCCH004
XCCH004

Reputation: 323

Define one table as priority over another table and then choose the attribute based on table priority

I am trying to solve an issue where in our data we have an 'article' and it is present in two tables - one for size and one for color. I would like to prioritize one of those attributes over the other and if the article is in both tables pick the top record based on priority.

I have created a sample dataset and query that I am trying to work with. I would like to be able to prioritize the size table over the color table and if article '1123' exists in both tables - only return the size - this is a two table example but it could be 3 or 4 or more. Am I going about this the right way or is there a much more streamlined way?

create table ##art_size

(article varchar(4), size varchar(2))

insert into ##art_size values ('1123','s'); 

create table ##art_color

(article varchar(4), color varchar(5)) 

insert into ##art_color values ('1123','grn');

select 1 as 'priority', * from ##art_size;
select 2 as 'priority', * from ##art_color;

returns: 

```priority    article size
----------- ------- ----
1           1123    s

(1 row affected)

priority    article color
----------- ------- -----
2           1123    grn

(1 row affected);

Upvotes: 0

Views: 1068

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You might want:

select t1.article, t1.col1
from table1 t1
union all
select t2.article, t2.col1
from table2 t2
where not exists (select 1 from table1 t1 where t1.article = t2.article)
union all
select t3.article, t3.col1
from table3 t3
where not exists (select 1 from table1 t1 where t1.article = t3.article) and
      not exists (select 1 from table2 t2 where t2.article = t3.article) ;

Each time you add a new table, you need to add another condition in the additional query.

With indexes on article in each table, this is probably the fastest method. Do note however, that the columns being fetched should have the same type, or you might get type conversion errors.

Upvotes: 1

gotqn
gotqn

Reputation: 43636

Try this:

DROP TABLE IF EXISTS #art_size
DROP TABLE IF EXISTS #art_color

create table #art_size

(article varchar(4), size varchar(2))

insert into #art_size values ('1123','s'), ('1125','l'); 

create table #art_color

(article varchar(4), color varchar(5)) 

insert into #art_color values ('1123','grn'), ('1124','grn');


SELECT TOP 1 WITH TIES [article], [size]
FROM
(

    select 1 as 'priority', * from #art_size

    UNION ALL

    select 2 as 'priority', * from #art_color

) DS
ORDER BY DENSE_RANK() OVER(PARTITION BY [article] ORDER BY [priority])

enter image description here

Upvotes: 1

Joe Shark
Joe Shark

Reputation: 688

Not sure what you want the output to look like, but could you use COALESCE? eg

SELECT COALESCE(size.article, col.article) as [article], COALESCE(size.size, col.color) as [attribute]
FROM ##art_color col
FULL JOIN ##art_size size on col.article = size.article

Upvotes: 2

Related Questions