afropunk afropunk - 7 months ago 17
SQL Question

Triple Join unique results in each?

I'm in the process of writing 3 versions of a query, with slightly different conditions for each version. However I'd like to join them like in the join diagram supplied. I don't want any duplicates on any reports, so I specifically want A to have all unique results for A and that contain crossovers for A, B & C. I'd then like B to contain all unique results for B and crossovers for B & C. And finally C to have all unique results for C only.

enter image description here

The query itself runs fine and is structured as below:

enter image description here

Thought I''d menton it in case I've got the structure all wrong or potentially could have written it a better way?

Any help to crack this one would be greatly appreciated.

Answer

As you are learning SQL, the first thing you should be aware of is that you are using a join syntax that was outdated more than twenty years ago. It is strange that you learned this such; you must be using a very, very old SQL book. The proper way to join tables t1 and t2 is from t1 inner join t2 on t2.colx = t1.coly.

You say you have three groups of conditions on the table, which leads to three queries the result sets thereof you want to combine (which you could do with UNION ALL). But some records are in more than one set and you don't want duplicates. Is this all? You could achieve this with UNION, which unlike its sibling UNION ALL not only glues results together, but also removes duplicates:

select col1, col2, col3
from t1
join t2 on t2.colx = t1.coly
where t1.col4 = 4 or t1.col5 = 5
union
select col1, col2, col3
from t1
join t2 on t2.colx = t1.coly
where t1.col4 = 44 or t1.col5 = 55;

However, you can achieve this much simpler by combining the conditions:

select col1, col2, col3
from t1
join t2 on t2.colx = t1.coly
where t1.col4 = 4 or t1.col5 = 5 or t1.col4 = 44 or t1.col5 = 55;

And the last line can be simplified to

where t1.col4 in (4, 44) or t1.col5 in (5, 55);

by the way. Is that already what you are after or is there something more?

UPDATE: From your comments to your request I gather you want to kind of rank your records. What matches the first condition set is group A, then from the remaining records those matching the second condition set is B and the rest is C. Here is how:

select col1, col2, col3,
  case when t1.col4 = 4 or t1.col5 = 5 then 'A'
       when t1.col4 = 44 or t1.col5 = 55 then 'B'
       else 'C'
  end as grp
from t1
join t2 on t2.colx = t1.coly
where t1.col4 in (4, 44, 444) and t1.col5 in (5, 55, 555);

Or more generally:

select col1, col2, col3,
  case when <condition set 1> then 'A'
       when <condition set 2> then 'B'
       else 'C'
  end as grp
from t1
join t2 on t2.colx = t1.coly
where (<condition set 1>) or (<condition set 2>) or (<condition set 3>);

And one more update: If you want to select C alone, simply apply all conditions as follows:

select col1, col2, col3
from t1
join t2 on t2.colx = t1.coly
where (<condition set 3>)
and not (<condition set 1>) 
and not (<condition set 2>);