asked on

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.

IE:
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

Guy Hengel [angelIII / a3]

so, you want to find out the "pairs" of values that appear in the same group(s)?

rita_corp

ASKER

yes (pardon) pairs of items (two items or greater) in the same group(s)

ASKER CERTIFIED SOLUTION

Fugas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

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_corp

ASKER

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!