Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

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
0
rita_corp
Asked:
rita_corp
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you want to find out the "pairs" of values that appear in the same group(s)?
0
 
rita_corpAuthor Commented:
yes (pardon) pairs of items (two items or greater) in the same group(s)
0
 
FugasCommented:
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
 
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


0
 
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now