Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Complex Query

Author  Topic 

DanG
Starting Member

10 Posts
Posted - 2003-04-14 : 16:11:57
Given the following table with 2 columns

Table
---------------------
KeyA KeyB
1 A
1 B
2 B
2 C
3 C
3 D
4 E
4 F
5 G
6 H
7 I
8 G
9 A
9 H

One can groups KeyA into 3 collections:
1,2,3 & 9
4
5 & 6

The groups are collection common elements of KeyB. Notice for example that 1 & 3 don’t have any common elements but still belong to the same group as they indirectly share elements in 2

The Objective is to have a query that list all keyA’s that belong to the same collection
For example: given KeyA = 2 (or 2 or 3) the selection should return
KeyA
-------
1
2
3
9

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-14 : 16:24:29
My brain hurts reading this one.

quote:

Notice for example that 1 & 3 don’t have any common elements but still belong to the same group as they indirectly share elements in 2


HUH?

1 A
1 B
2 B
2 C
3 C
3 D

1 has A,B
2 has B,C
3 has C,D

What are you talking about?


Brett

8-)
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2003-04-14 : 16:33:35
Huh?
Aren't there four groups here:
1,2,3,9,6 (A,B,C,D,H)
4 (E,F)
5,8 (G)
7 (I)

(I think he's talking about connected components of a bipartite graph, but I'm not sure!)


Edited by - Arnold Fribble on 04/14/2003 16:34:32
Go to Top of Page

DanG
Starting Member

10 Posts
Posted - 2003-04-14 : 16:33:55
quote:

My brain hurts reading this one.

quote:

Notice for example that 1 & 3 don’t have any common elements but still belong to the same group as they indirectly share elements in 2


HUH?

1 A
1 B
2 B
2 C
3 C
3 D

1 has A,B
2 has B,C
3 has C,D

What are you talking about?


Brett

8-)


-----------------------------------------------------------------
Well,

1 has A,B
2 has B,C
3 has C,D
9 has A,H

so 1 & 2 share B and 2 & 3 share C, hence 2 belongs to 1 & 3, hence 1, 2 & 3 are in the same collection
9 does also share A, hence it also belongs to the same group.

Logical, isn't it? ;-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-14 : 16:42:54
You're connecting 1 and 3 because of their relationship to 2? This looks a lot like the adjancy model or a hierarchal one (parent/child). Although with your model it could almost be recursive.

What problem is this meant to address, or is it just a mathmatical exercise? And how would you resolve recursion?

Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-04-14 : 16:44:34
If anyone can solve this, Arnold can!

There's definitely a need for iteration or recursion here.


declare @tA (keyA int)
declare @tB (keyB char(1))

declare @StartGroup int;
declare @Done int;

set @StartGroup =2
set @Done = 0
-- starting point:

insert into @tA
select keya, keyb
from YourTable
where KeyA = @StartGroup

-- here, we loop:

while done = 0
begin

insert into @tB select keyB from YOurTable
where KeyA in (select keya from @tA) and
keyb not in (select keyb from @tB)

insert into @tA select keyA from YourTable
where keyB in (select KeyB from @tB) and
keyA not in (select KeyA from @tA)

if @@Rowcount = 0 set @done = 1
end

select * from @tA

something like that ... very ugly .... sorry don't have SQL Server running here to do any actual testing.

- Jeff

Edited by - jsmith8858 on 04/14/2003 16:45:52
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-14 : 16:49:37
I'm talking about having to gaurd against inifinte recursiveness (word?). You could get stuck in a loop ad infintum Unless I guess you eliminate values from your base table as you allocate them. But in the next breath, you may eliminate something that you might need later on for a different "collection". And it seems thing might be in mupltiple collections.

I still want to know if this is a real world application or not.



Brett

8-)
Go to Top of Page

DanG
Starting Member

10 Posts
Posted - 2003-04-14 : 16:50:32
quote:

You're connecting 1 and 3 because of their relationship to 2? This looks a lot like the adjancy model or a hierarchal one (parent/child). Although with your model it could almost be recursive.

What problem is this meant to address, or is it just a mathmatical exercise? And how would you resolve recursion?

Brett

8-)



------------------


This is a "simplification" of a real problem.

I actually have 3 table with a many to many relationship. The table I've given is the in-between table.
My tables represents invoices - OrderParts. You may have several OrderParts on an invoice and many invoice for an OrderPart.
To have a total view I need the Orders and invoices that are in the same group as my example (tries to) show(s)...
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2003-04-14 : 16:51:59
quote:

If anyone can solve this, Arnold can!

Damn, I was just thinking that Jeff would maybe chime in with a great solution...
I hate graphs. Graphs are why I'm not Dr. Arnold Fribble, well, graphs and category theory and algebraic program transformation and laziness (both kinds).
Go to Top of Page

DanG
Starting Member

10 Posts
Posted - 2003-04-14 : 16:54:52
quote:

If anyone can solve this, Arnold can!

There's definitely a need for iteration or recursion here.


declare @tA (keyA int)
declare @tB (keyB char(1))

declare @StartGroup int;
declare @Done int;

set @StartGroup =2
set @Done = 0
-- starting point:

insert into @tA
select keya, keyb
from YourTable
where KeyA = @StartGroup

-- here, we loop:

while done = 0
begin

insert into @tB select keyB from YOurTable
where KeyA in (select keya from @tA) and
keyb not in (select keyb from @tB)

insert into @tA select keyA from YourTable
where keyB in (select KeyB from @tB) and
keyA not in (select KeyA from @tA)

if @@Rowcount = 0 set @done = 1
end

select * from @tA

something like that ... very ugly .... sorry don't have SQL Server running here to do any actual testing.

- Jeff

Edited by - jsmith8858 on 04/14/2003 16:45:52



-----------------------------

Yup, I've written something similar, but I was wondering if this could be achieved with a single select...
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-14 : 16:57:13
Well, I wouldn't say simplified is the right word.

Can you post your table ddl (preferable with PK and FK relationships).

But it sounds like you have:

Invoice
InvoiceId
cols..

OrderParts
OrderId
PartId
cols..

Orders
OrderId
cols..

Parts
PartsId
cols..


And the Mother of all tables (your original post):

InvoiceOrderParts
InvoiceId
OrderId
PartsId
cols...

But I'm SURE however, you decided to use surrogate keys....

Let us know. I know these guys and gals can figure it out (haven't seen them fail yet).



Brett

8-)
Go to Top of Page

DanG
Starting Member

10 Posts
Posted - 2003-04-14 : 16:57:53
quote:

I'm talking about having to gaurd against inifinte recursiveness (word?). You could get stuck in a loop ad infintum Unless I guess you eliminate values from your base table as you allocate them. But in the next breath, you may eliminate something that you might need later on for a different "collection". And it seems thing might be in mupltiple collections.

I still want to know if this is a real world application or not.

-------------------


No risk of that as the collections can not overlap. If there whould be any common elements, they would actually be part of a larger collection

Brett

8-)
Go to Top of Page

DanG
Starting Member

10 Posts
Posted - 2003-04-14 : 17:04:32
quote:

Well, I wouldn't say simplified is the right word.

Can you post your table ddl (preferable with PK and FK relationships).

But it sounds like you have:

Invoice
InvoiceId
cols..

OrderParts
OrderId
PartId
cols..

Orders
OrderId
cols..

Parts
PartsId
cols..


And the Mother of all tables (your original post):

InvoiceOrderParts
InvoiceId
OrderId
PartsId
cols...

But I'm SURE however, you decided to use surrogate keys....

Let us know. I know these guys and gals can figure it out (haven't seen them fail yet).



Brett

8-)



-----------------------------------


Something like this:

Invoice(InvoiceID) -< InvoiceLine(InvoiceID,InvoiceLineID)
Order(OrderID) -< OrderPart(OrderId,OrderPartID)
InvoiceLine -< cross(InvoiceID,InvoiceLineID,OrderId,OrderPartID) >- OrderPart
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-04-15 : 08:56:20
DDL?

I do see what you mean though...so it begs the question:

What is your ultimate goal? Can you describe what you need from what tables? You seem to have a fairly straight forward ER Model....


Brett

8-)
Go to Top of Page

DanG
Starting Member

10 Posts
Posted - 2003-04-17 : 18:02:08
quote:

DDL?

I do see what you mean though...so it begs the question:

What is your ultimate goal? Can you describe what you need from what tables? You seem to have a fairly straight forward ER Model....


Brett

8-)



---------------

I need the collection of all Invoices of the same group, so I can totalize the lines on all related invoices and order parts.
I solved this by adding columns to Invoice & OrderPart tables and some triggers to update them when modifications are made.
This way queries are fast and the overhead limited. Of course a single select whould have been nicer, but life wouldn't be so nice if everything was too easy ;-)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site