SQL Same Values but different secondary values

Question

I've tried this for hours and different forums but cant get my head around to figure out this. I've tried joins and all sorts but i constantly got wrong results.

My sample table looks like below

Item Branch  Type
A    Main    Box
A    London  Single
A    Paris   Single
A    Tokyo   Box
B    Main    Single
B    London  Single
B    Paris   Single
C    Main    Single
C    London  Box
C    Paris   Single
C    Tokyo   Single
D    Main    Box
D    London  Single
D    Paris   Box
D    Tokyo   Box
D    Sydney  Single

what I want to get as my result set is all Items that has a Different 'Type' to its Main 'Branch'

For example if Main Type is Box, display Main branch and other branches that has different Types

result table should look like below.

Item Branch  Type
**A    Main    Box**
A    London  Single
A    Paris   Single
**C    Main    Single**
C    London  Box
**D    Main    Box**
D    London  Single
D    Sydney  Single

any help is much appreciated.

thanks

*"I've tried joins and all sorts..."* There are a lot of helpful people on SO who won't bother to answer unless you *show* what you've tried. You're also likely to get more responses if you include CREATE TABLE and INSERT statements in your question. — Mike Sherrill 'Cat Recall', Oct 28 '16 at 13:29
I'm not sure I understand. Why aren't any records from Item B included in the results? — David Rushton, Oct 28 '16 at 13:31
@destination-data Hi Any records from B aren't in results because 'B's are in same type as its main branch in all other branches whereas other items are present a different type in other branches. hope this explains it — Sinnerv, Oct 28 '16 at 13:44

Vamsi Prabhala · Accepted Answer · 2016-10-28T14:06:44.507

This is one way to do it to get the rows where the type on any branch differs from the main branch.

select * 
from t t1 
where (branch='Main' or (branch <> 'Main' 
                         and exists (select 1 from t 
                                     where item=t1.item and branch = 'Main' 
                                     and type <> t1.type)
                         )
       )
and item in (select item from t group by item having count(distinct type) > 1)

If the query above seems confusing, you can use a cte to get all the non - main branches whose type differs from the main branch for an item. Thereafter use union all to get the main branch rows for those items.

with y as (
select * from t t1
where branch <> 'Main' and exists (select 1 from t 
                                   where item=t1.item and branch = 'Main' 
                                   and type <> t1.type)
    )
select * from t where branch='Main' and item in (select item from y)
union all
select * from y

Thanks ever so much for this. it works perfect!! I tried the, nested select query bit as separate query and tried joining it with the big table but I got confused trying to match columns and how to name them etc. also I discovered the Count function from other similar posts but never could get my head around to bring all these together. — Sinnerv, Oct 28 '16 at 14:07

score 0 · Answer 2 · answered Oct 28 '16 at 13:40

0

select A.item, A.branch, A.type 
from ibt A, ibt B 
on A.item = B.item and B.branch = "Main" and A.type != B.type;

the results are as blow:

A|London|Single
A|Paris|Single
C|London|Box
D|London|Single
D|Sydney|Single

answered Oct 28 '16 at 13:40

SmartXiaoMing

121
1
6

2

There's a great discussion in this [question](http://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-dont-use-the-join-keyword-in-sql-or-my) on the older style of join you've used in your example. Tl;dr Avoid them. Use ANSI joins instead. – David Rushton Oct 28 '16 at 13:48

score 0 · Answer 3 · answered Oct 28 '16 at 15:11

I am not sure if I understood what you need. However, here is a possible partial solution. You can improve this simple with your requirement.

Sample in postgre.

CREATE TEMPORARY  TABLE items (
  item varchar(10),
  branch varchar(10),
  type varchar(10)
);


INSERT INTO items VALUES ('A', 'Main', 'Box');
INSERT INTO items VALUES ('A', 'London', 'Single');
INSERT INTO items VALUES ('A', 'Paris', 'Single');
INSERT INTO items VALUES ('A', 'Tokyo', 'Box');
INSERT INTO items VALUES ('B', 'Main', 'Single');
INSERT INTO items VALUES ('B', 'London', 'Single');
INSERT INTO items VALUES ('B', 'Paris', 'Single');
INSERT INTO items VALUES ('C', 'Main', 'Single');
INSERT INTO items VALUES ('C', 'London', 'Box');
INSERT INTO items VALUES ('C', 'Paris', 'Single');
INSERT INTO items VALUES ('C', 'Tokyo', 'Single');
INSERT INTO items VALUES ('D', 'Main', 'Box');
INSERT INTO items VALUES ('D', 'London', 'Single');
INSERT INTO items VALUES ('D', 'Paris', 'Box');
INSERT INTO items VALUES ('D', 'Tokyo', 'Box');
INSERT INTO items VALUES ('D', 'Sydney', 'Single');

SELECT
CASE WHEN T.branch = 'Main' THEN rpad(T.item, length(T.item) + T.children::INT, '*') ELSE T.item END item,
T.branch,
T.type
FROM (
SELECT
  I.item,
  I.branch,
  I.type,
  MAIN_ITEMS.item                              mitem,
  MAIN_ITEMS.branch                            mbranch,
  MAIN_ITEMS.type                              mtype,
  (SELECT count(j.item)
   FROM items j
   WHERE I.ITEM = j.item AND I.type <> j.type) children    FROM items I
  JOIN (
         SELECT
           item,
           branch,
           type
         FROM items
         WHERE branch = 'Main'

       ) MAIN_ITEMS
    ON ((I.ITEM = MAIN_ITEMS.item AND I.type <> MAIN_ITEMS.type) OR (
    I.ITEM = MAIN_ITEMS.item AND I.branch = MAIN_ITEMS.branch AND I.type = MAIN_ITEMS.type
  )
    )
) T
where T.children > 0;

Output:

A** Main    Box
A   London  Single
A   Paris   Single
C*  Main    Single
C   London  Box
D** Main    Box
D   London  Single
D   Sydney  Single

I hope that this sample help you.

SQL Same Values but different secondary values

3 Answers3