Logically create templates based on intersection table

Question

I'm not sure how to describe the question, but I'm trying to give suggestions on what codes to attribute to tariffs. I'm doing this in Oracle.

Here's my database structure:

CODE (
    CODEID *PK NCHAR(10)
)

CODETARIFF (
    TARIFFNO NCHAR(15) *PK *FK
    CODEID NCHAR(10) *PK *FK
)

TARIFF (
    TARIFFNO NCHAR(15) *PK
)

So I'm trying to logically make templates for what codes should be assigned to tariffs. I'm imagining showing something along the lines of: "6 tariffs also have these 2 codes associated with them"

I tried this, but the counts that are returned for each of the codes aren't really showing a template, they only really show incidences of when that one code appears with the two I've specified.

SELECT COUNT(*), CodeID
FROM CodeTariff
  INNER JOIN (
    SELECT TariffNo, COUNT(*) 
    FROM CodeTariff
    WHERE CodeID IN ('ABC', 'DEF') 
    GROUP BY TariffNo
    HAVING COUNT(*) > 1) SQ 
  ON CodeTariff.TariffNo = SQ.TariffNo 
WHERE CodeID NOT IN ('ABC', 'DEF')
GROUP BY CodeTariff.CodeID
ORDER BY COUNT(*) DESC;

Sorry if this is confusing.

I don't know if this is even possible, but I'm looking for output like this:

Data: TariffCode

TariffNo        CodeID

1111            ABC
1111            DEF
2222            ABC
2222            DEF
2222            GHI
2222            JKL
3333            ABC
3333            DEF
3333            GHI
3333            JKL

Output: (when given tariff 1111)

CodesToAdd      Count

GHI, JKL        2

So that I can display:

2 other tariffs have the codes GHI and JKL associated with them. Would you like to add these codes to tariff 1111?

Perhaps some sample data with expected outcome might make this clearer? — Tom H, Oct 27 '11 at 19:38
Is this right? : The sequence of events is: (1) the user creates a new tariff; (2) the user tags the tariff with some existing codes (`'ABC'` and `'DEF'`); (3) the app gives the user some suggestions for other codes that (s)he might want to tag the tariff with. The logic for these suggestions is, the app finds already-existing tariffs that are tagged with both `'ABC'` and `'DEF'`, and sees what other codes are most commonly found among those already-existing tariffs. If that's correct, then your query looks right to me . . . for each code, it shows how many existing tariffs are tagged with it. — ruakh, Oct 27 '11 at 19:45
Yeah, but there isn't anything that says that a certain number of tariffs are tagged with one or more codes, there's no real linkage. I guess the current query makes sense though. — tedski, Oct 27 '11 at 20:03
I think I see. The reason you want to suggest `'GHI'` and `'JKL'` isn't that they're *each* used on two tariffs that have `'ABC'` and `'DEF'`, but because the two of them are used *together* on two tariffs that have `'ABC'` and `'DEF'`? — ruakh, Oct 27 '11 at 20:09
So what would you want to display if tariff #3333 were also tagged as MNO? — ruakh, Oct 27 '11 at 20:31

Greg · Answer 1 · 2011-10-27T21:22:36.047

Try these magics out:

SELECT     Code, COUNT(*) AS Count
FROM         (SELECT     dbo.TariffCode.Tariff, dbo.TariffCode.Code
                   FROM          dbo.TariffCode LEFT OUTER JOIN
                                              (SELECT     TariffCode_2.Tariff, TariffCode_2.Code
                                                FROM          dbo.TariffCode AS TariffCode_2 INNER JOIN
                                                                           (SELECT     Tariff, Code
                                                                             FROM          dbo.TariffCode AS TariffCode_1
                                                                             WHERE      (Tariff = '1111')) AS TariffsWithSharedCodes ON TariffCode_2.Code = TariffsWithSharedCodes.Code AND 
                                                                       TariffCode_2.Tariff <> '1111') AS MutualCodes ON dbo.TariffCode.Tariff = MutualCodes.Tariff AND 
                                          dbo.TariffCode.Code = MutualCodes.Code
                   WHERE      (MutualCodes.Code IS NULL) AND (dbo.TariffCode.Tariff <> '1111')) AS MissingCodes
GROUP BY Code
ORDER BY Count DESC, Code

This is T-SQL, sorry, but you'll get the idea

score 0 · Answer 2 · edited Oct 28 '11 at 09:01

Hope the script below can help you out. It will get all possible tariffs, not only for '1111':

with temp as (
  select tariffno, tariffno2, codeid 
  from (
    select distinct c1.tariffno, c2.tariffno as tariffno2, c2.codeid
    from tariffcode c1
    join tariffcode c2 on c1.tariffno != c2.tariffno and c1.codeid != c2.codeid 
  ) c1 
  where 
    not exists (select 1 from tariffcode where tariffno = c1.tariffno and codeid = c1.codeid)
)
select tariffno, codeid, count(*) as cnt from temp group by tariffno, codeid;

Logically create templates based on intersection table

2 Answers2