DB Select - Logic Meltdown!

Hello Experts - I've been struggling with this one for a while.
[sample table]
ITEM         GROUP
4            A
7            A
3            A
8            A
7            B
6            B
9            B
0            C
4            C
2            C
5            C
4            C
7            C
5            D
9            D
2            E
7            E
1            E
4            E
7            F
3            F
9            F
6            F
8            G
4            H
5            H
3            H
9            H
0            H
8            H

I need an sql query that will tell me how many times each "ITEM", on a range of SELECT DISTINCT ITEM appeared with one another in a particular group.  

Items 4 and 8 appeared 2 times (groups A and H).
Items 0 and 4 appeared 2 times (groups C and H).
Items 7, 6 and 9 appeared appeared 2 times (groups B and F).

Can it be done?  Thanks
Who is Participating?
FugasConnect With a Mentor Commented:
Hi, I don't know what you really need, but this could help you
select item, group_concat(DISTINCT `group` order by `group` ASC) from items group by item;
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you want to find out the "pairs" of values that appear in the same group(s)?
rita_corpAuthor Commented:
yes (pardon) pairs of items (two items or greater) in the same group(s)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's try:
select i.item item_1, i2.item_2, i.group group_1, i2.group group_2
from yourtable i
join yourtable oi
  on o.group = i.group
 and o.item <> i.item
join yourtable og
  on og.item = i.item
  and og.group <> i.group
join yourtable i2
  on i2.item = oi.item
 and i2.group = og.group 

Open in new window

rita_corpAuthor Commented:
Wow - what a gem group_concat was.  Definitely have application for this in the future!  I'm trying to see if I can get this functionality over oracle, but this was a fantastic start!
All Courses

From novice to tech pro — start learning today.