Solved

Cross Table , Interchange information

Posted on 2008-09-29
14
343 Views
Last Modified: 2011-10-19
I've been cracking my head for some time on this now,
I need to record interchange infomration:
the records come in like this:
Record Type1: Item 'Q' = Item 'W'  ::Item Q is the same as item W
Record Type2: Item 'A' -> Item 'B' ::Item A Replaced by Item B

Now my guest guess was to seperate this into 2 tables
Where Type 1:
Item | Group
'1'   |  'A'
'2'   |  'B'

'3'   |  'C'
'4'   |  'C'
'5'   |  'C'

If i want to see #4 then trhough group i get "3,4,5" all '='


Type 2:
Item | Into Item
  '1' | '2'    
  '1' | '3'
  '4' | '5'
If i want to see #1 then though i get "2,3" all  replace '1'

Now here comes the problem
1.With close to a million records it takes a really of a long time to process,
1.1 I Create an array in keep looping throught table 1 and 2 untill no new records are found
     Here is now it works Based on the above tables

Query:#1
:  1
:2 | 3,4
:6 | 5

*** I also Identify loops with this procedure, if i have records like "5 ->1"
0
Comment
Question by:CMDAI
  • 8
  • 6
14 Comments
 
LVL 5

Expert Comment

by:jose_juan
ID: 22603303
Hi,

I don't understan what is table 2.

explain please.
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22603357
Tbale 2:
Title:       | FROM Item |   To Item   |
Record:  | Item "A" | Item "B" |

Item "B" can replace Item "A"
It is not the same as  A = B because
Item "A" canNot replace Item "B"
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22603413
Ok, CMDAI,

what query, do you like optimize?
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22603419
Initially i instead o making 2 tables i had only 1
It looked like this:

|-|Item "A"  |-|  = |-|  Item "B"|-|
|-|Item "C"  |-| ->  |-| Item "E"|-|
|-|Item "C"  |-| ->  |-| Item "D"|-|
|-|Item "D"  |-| ->  |-| Item "W"|-|
|-|Item "A"  |-| =  |-| Item "C"|-|
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22603454
1:Item | Group
'1'   |  'A'
'2'   |  'B'
'3'   |  'C'
'4'   |  'C'
'5'   |  'C'

2:Item | Into Item
  '1' | '2'    
  '1' | '3'
  '4' | '5'


If i want to get all the items that replace #1
I start a loop in VB and collect and Array

1,(                  ) * Initial
2,(from Table 2) *Query  1
3,(from Table 2) * Query 1
4,(from Table 1) * Query 2
5,(from Table 1) * Query 2
5,(from Table 2)  * Query 3
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22603467
Sorry typo:
1,(                  ) * Initial
2,(from Table 2) *Query  1
3,(from Table 2) * Query 1
4,(from Table 1) * Query 2
5,(from Table 1) * Query 2
6,(from Table 2)  * Query 3
Then it stops as no new numbers canbe found
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22603569
If I covert everything into table 2 format this is what i am trying to do
but i cannot predict how long the cross is going to be.
Cross.jpg
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 5

Accepted Solution

by:
jose_juan earned 500 total points
ID: 22603581
Ok, that's all,

you have T1 { item, group } and T2 { fromitem, toitem }

you need two final relations, item equal item, and item replaced with item.

for first relation I suggest generate other final table Q1 { itemK, item } when itemK is the "key item" for all equality item group. (if 1=2 and 2=3 then 1=3)

the method for populate a minimal Q1 table are

CREATE TABLE Q1 ( itemK <type>, item <type> )

INSERT INTO Q1 (
    itemK,
    item
)
SELECT    g.itemK,
    q.item
FROM    (
    SELECT    group, itemK = MIN( item )
    FROM    T1
    GROUP
    BY    group
) g
JOIN    Q1 q
ON    g.group    = q.group

result example

itemK    item
------- -------
1    1
2    2
3    3
3    4
3    5

not iterate is needed (only replace group for itemK).

For replacement relation (like 1 -> 4) create other final table Q2 { fromitemK, toitemK )

CREATE TABLE Q2( fromitemK <type>, toitemK <item> )

-- first population
INSERT INTO Q2 (
    fromitemK,
    toitemK
)
SELECT    DISTINCT
    fromitemK    = fk.itemK,
    toitemK        = tk.itemK
FROM    T2 t
JOIN    Q1 fk
ON    t.fromitem    = fk.item -- only one match
JOIN    Q2 tk
ON    t.toitem    = tk.item -- only one match

-- regenerate relations of relations
WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO Q2 (
        fromitemK,
        toitemK
    )
    SELECT    DISTINCT
        fromitemK    = fk.itemK,
        toitemK        = tk.itemK
    FROM    Q2 t1
    JOIN    Q2 t2
    ON    t1.toitemK    = t2.fromitemK
    JOIN    Q1 fk
    ON    t1.fromitem    = fk.item -- only one match
    JOIN    Q2 tk
    ON    t2.toitem    = tk.item -- only one match
    WHERE    NOT EXISTS (
            SELECT    *
            FROM    Q2
            WHERE    fromitemK    = fk.itemK
            AND    toitemK        = tk.itemK
        )
END

this is all you need!

* Query for a key item @K

-- all @K = item
SELECT    item
FROM    Q1
WHERE    itemK = ( SELECT itemK FROM Q1 WHERE item = @K )

-- all @K -> item
SELECT    toitem
FROM    Q1
WHERE    fromitemK = ( SELECT itemK FROM Q1 WHERE item = @K )

-- all item -> @K
SELECT    fromitem
FROM    Q1
WHERE    toitemK = ( SELECT itemK FROM Q1 WHERE item = @K )

Good luck!


0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22603607
Ops!

"@K -> item" and  "item -> @K" querys are "FROM Q2" instead "FROM Q1" (obviously).

And check your index!!!!!! optimize.

The running cost are linear over initial total row count.

Good luck!

0
 
LVL 1

Author Comment

by:CMDAI
ID: 22603839
Sorry i'm trying to recreate but i got stuck on this part

""""""""""""""""""""""""""""""""""""""
JOIN    Q1 q
ON    g.group    = q.group
""""""""""""""""""""""""""""""""""""""""



From:
INSERT INTO Q1 (
   itemK,
   item
)
SELECT    g.itemK,
   q.item
FROM    (
   SELECT    group, itemK = MIN( item )
   FROM    T1
   GROUP
   BY    group
) g
JOIN    Q1 q
ON    g.group    = q.group
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22603856
Yes, group field only is in T1, you must write

JOIN T1 q
ON g.group = q.group

Sorry!
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22621267
Sorry i wans't arround yestruday,
I tried to recreate but got confused , maybe because im doing it in access
and the language structure is different.

So Q1 is
[Key Item] || [Item]
Basically it looks like the [group] has been replaced by one of the items in the [group]

Your result example:
itemK    item
------- -------
1    1
2    2
3    3
3    4
3    5
Will this also be Inside?
4    4
4    3

THen Q2 I'm confused
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22621919
Oh! Access... yes.
I suggest to you use a better database engine(like mysql, ms sql server 2005 express, ...).

Nevertheless, the only important change are the WHILE iteration and you can make this using the WHILE VB sentence.

On the other hand, my result example are good, only one itemK is needed for "linked relations". Tuples (3,4), (4,3) are equals and only one must be in table.

The Q1 table are a simple translation of your Type1 table.
For all ( itemK, item ) tuples than itemK<>item, then item must not be in Q2 (not is needed, if itemK are in Q2 then I deduce item are also in Q2).

A complete example:

Type1 relations
----------------------
1 A
2 B
3 A
4 C
5 B
6 D
7 B

Type2 relations
----------------------
3 7
5 6
4 6

Then we generate Q1 table as a simple translation of id group:

Minimum key for each Id group
--------------------------------
1 A
2 B
4 C
6 D

Q1
-----
1 1
1 3
2 2
2 5
2 7
4 4
6 6

In Q2, only itemK!

Q2 (+first insert)
--------
1 2
2 6
4 6

Q2 (+second insert)
--------
1 2
2 6
4 6
1 6

Stop!

Consequences:

1 and 3 elements are equals and equivalents.
2, 5 and 7 elements are equals and equivalents.
4 are alone.
6 are alone.

1 or 3 element can be replaced with any of 2, 5 and 7.
2, 5 or 7 element can be replaced with 6.
4 element can be replaced with 6.
1 or 3 element can be replaced with 6.

If I have not a error...

Good luck!
0
 
LVL 1

Author Closing Comment

by:CMDAI
ID: 31501456
Thank's for your help, sorry for long responsiveness, had wasnt arround for a long time,
Works great now ! :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Union 20 44
check the deletion of SQL job on who delete/disable it 12 29
Updating a table from a temp table 4 29
Help Parsing a String with SQL Syntax 23 32
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now