Solved

# DB Select - Logic Meltdown!

Posted on 2011-09-17
411 Views
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
0
Question by:rita_corp
• 2
• 2

LVL 142

Expert Comment

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

Author Comment

ID: 36554711
yes (pardon) pairs of items (two items or greater) in the same group(s)
0

LVL 4

Accepted Solution

Fugas earned 500 total points
ID: 36555707
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;
0

LVL 142

Expert Comment

ID: 36556742
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
``````

0

Author Closing Comment

ID: 36558187
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!
0

## Featured Post

### Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…