tables between A-B and A-C.
The data in A and C changes rarely, and the A-C relationship relates
all possible combinations of A to a C
If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
contain the records:
A.1, C.1
A.2, C.2
A.3, C.3
A.1, C.4
A.2, C.4
A.1, C.5
A.3, C.5
A.2, C.6
A.3, C.6
A.1, C.7
A.2, C.7
A.3, C.7
so that any set of records from A (including the empty set) relates to
exactly on record in C
and suppose that B contains records from B.1 to B.3, and A-B contains
records
A.2, B.1
A.1, B.2
A.3, B.2
What I am having touble doing is crafting a query that will take me
from a record in B to the record in C that has the corrisponding set
of records in A-C as is in A-B for the chosen B.
ie, I want a query that will give me a result set something like
B.1, C.2
B.2, C.5
B.3, C.8
As far as I can come up with, this is not doable in a single query,
but perhaps I am missing something...
Simon WithersPlease post DDL (CREATE TABLE statements) for these tables - simplified if
possible but including keys and constraints. Also post some sample data as
INSERT statements. It will save you a lot of typing and make it easier for
others to understand your problem.
--
David Portas
----
Please reply only to the newsgroup
--|||Create/Insert scripts:
CREATE TABLE [Product] (
[ProductID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Project] (
[ProjectID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED
(
[ProjectID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [ProjectProduct] (
[ProjectID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_ProjectProduct] PRIMARY KEY CLUSTERED
(
[ProjectID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProjectProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_ProjectProduct_Project] FOREIGN KEY
(
[ProjectID]
) REFERENCES [Project] (
[ProjectID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [SubledgerID] (
[SubledgerID] [int] NOT NULL ,
[Name] [char] (10) COLLATE Compatibility_52_409_30003 NULL ,
CONSTRAINT [PK_SubledgerID] PRIMARY KEY CLUSTERED
(
[SubledgerID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [SubledgerProduct] (
[SubledgerID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_SubledgerProduct] PRIMARY KEY CLUSTERED
(
[SubledgerID],
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_SubledgerProduct_Product] FOREIGN KEY
(
[ProductID]
) REFERENCES [Product] (
[ProductID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_SubledgerProduct_SubledgerID] FOREIGN KEY
(
[SubledgerID]
) REFERENCES [SubledgerID] (
[SubledgerID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
INSERT INTO [Product] ([ProductID], [Name]) VALUES (1, 'Product A')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (2, 'Product B')
INSERT INTO [Product] ([ProductID], [Name]) VALUES (3, 'Product C')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (1, 'Project 1')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (2, 'Project 2')
INSERT INTO [Project] ([ProjectID], [Name]) VALUES (3, 'Project 3')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (1, 'Subl a')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (2, 'Subl b')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (3, 'Subl c')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (4, 'Subl d')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (5, 'Subl e')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (6, 'Subl f')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (7, 'Subl g')
INSERT INTO [SubledgerID] ([SubledgerID], [Name]) VALUES (8, 'Subl h')
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (1, 3)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 1)
INSERT INTO [ProjectProduct] ([ProjectID], [ProductID]) VALUES (2, 3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (1,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (2,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (3,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (4,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (5,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (6,
3)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
1)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
2)
INSERT INTO [SubledgerProduct] ([SubledgerID], [ProductID]) VALUES (7,
3)
Desired Output:
SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM ???
Producing:
Project 1, 1, Subl c, 3
Project 2, 2, Subl f, 6
Project 3, 3, Subl h, 8
Simon Withers
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||try this script
SELECT Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID FROM Project , SubledgerID , ProjectProduct,
SubledgerProduct WHERE SubledgerProduct.ProductID IN
( SELECT ProjectProduct.ProductID FROM ProjectProduct WHERE
ProjectProduct.ProjectID = Project.ProjectID )
AND SubledgerID.SubledgerID=SubledgerProduct.Subledger ID GROUP BY
Project.Name, Project.ProjectID, SubledgerID.Name,
SubledgerID.SubledgerID
it will produce
Project 1 1 Subl c 3
Project 1 1 Subl e 5
Project 1 1 Subl f 6
Project 1 1 Subl g 7
Project 2 2 Subl a 1
Project 2 2 Subl c 3
Project 2 2 Subl d 4
Project 2 2 Subl e 5
Project 2 2 Subl f 6
Project 2 2 Subl g 7
is it ok?
--
..bucho
ICQ#: 38153136
Distributed.net RC5-72
ID: support@.1580.ru|||I'm not sure I've understood your requirement correctly, but does this help:
SELECT DISTINCT PR.name, PR.projectid, SL.name, SL.subledgerid
FROM Project AS PR
JOIN ProjectProduct AS PP
ON PP.projectid = PR.projectid
JOIN SubLedgerProduct AS SP
ON SP.productid = PP.productid
JOIN SubLedgerID AS SL
ON SL.subledgerid = SP.subledgerid
--
David Portas
----
Please reply only to the newsgroup
--|||[posted and mailed, please reply in news]
What a devilish problem! Took me quite some time understand what you
are looking for!
What I am offering is only a partial solution, and may even prove to be
a non-solution. It looks like this:
SELECT a.SubledgerID, b.ProjectID
FROM (SELECT s.SubledgerID, sumid = SUM(coalesce(sp.ProductID, 0)),
cnt = COUNT(sp.ProductID)
FROM SubledgerID s
LEFT JOIN SubledgerProduct sp ON s.SubledgerID = sp.SubledgerID
GROUP BY s.SubledgerID) AS a
JOIN (SELECT p.ProjectID, sumid = SUM(coalesce(pp.ProductID, 0)),
cnt = COUNT(pp.ProductID)
FROM Project p
LEFT JOIN ProjectProduct pp ON pp.ProjectID = p.ProjectID
GROUP BY p.ProjectID) AS b
ON a.sumid = b.sumid
AND a.cnt = b.cnt
It works that you get the same signature on ProductID no matter you
come from Subledger or you come from Project.
Now, the trouble here is finding a signature which does not give any false
positive. I know that there a similar problem, and I offered a suggestion
that Steve Kass proved not be safe. Unfortunately, I don't remember if
we ever found something that was waterproof. This time I didn't even
try, but I kind of hope that Steve who is a mathematician jumps in.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Simon,
This is my solution. I used your first example (because of its shorter
names!)
It's funny that we don't need table A at all. All we need is its
relations to B and C.
select
b,
c = (select C.c
from C left outer join AC on (C.c = AC.c and
a in (select a
from AB
where b = B.b)
)
group by C.c
having count(a) = (select count(*) from AB where b = B.b)
and count(a) = (select count(*) from AC where AC.c = C.c)
)
from B
Good Luck,
Shervin
simon@.klickit.com (Simon Withers) wrote in message news:<78088784.0309220828.62c349e4@.posting.google.com>...
> I have 3 data tables, A, B and C, with many to many relationship
> tables between A-B and A-C.
> The data in A and C changes rarely, and the A-C relationship relates
> all possible combinations of A to a C
> If A contains A.1 to A.3 and C contains C.1 - C.8 then A-C could
> contain the records:
> A.1, C.1
> A.2, C.2
> A.3, C.3
> A.1, C.4
> A.2, C.4
> A.1, C.5
> A.3, C.5
> A.2, C.6
> A.3, C.6
> A.1, C.7
> A.2, C.7
> A.3, C.7
> so that any set of records from A (including the empty set) relates to
> exactly on record in C
> and suppose that B contains records from B.1 to B.3, and A-B contains
> records
> A.2, B.1
> A.1, B.2
> A.3, B.2
> What I am having touble doing is crafting a query that will take me
> from a record in B to the record in C that has the corrisponding set
> of records in A-C as is in A-B for the chosen B.
> ie, I want a query that will give me a result set something like
> B.1, C.2
> B.2, C.5
> B.3, C.8
> As far as I can come up with, this is not doable in a single query,
> but perhaps I am missing something...
> Simon Withers|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> This is my solution. I used your first example (because of its shorter
> names!)
> It's funny that we don't need table A at all. All we need is its
> relations to B and C.
Very nice solution! It spent some time to convince myself that it
addresses the general problem and not just produces the desired output
by chance, so to speak.
I did find a need for a minor improvement:
SELECT ProjectID, SubledgerID
FROM (SELECT ProjectID,
SubledgerID =
(SELECT C.SubledgerID
FROM SubledgerID C
LEFT JOIN SubledgerProduct AC
ON C.SubledgerID = AC.SubledgerID
AND AC.ProductID IN (SELECT AB.ProductID
FROM ProjectProduct AB
WHERE AB.ProjectID = B.ProjectID)
GROUP BY C.SubledgerID
HAVING COUNT(AC.ProductID) = (SELECT COUNT(*)
FROM ProjectProduct AB
WHERE AB.ProjectID = B.ProjectID)
AND COUNT(AC.ProductID) = (SELECT COUNT(*)
FROM SubledgerProduct AC
WHERE AC.SubledgerID =
C.SubledgerID)
)
FROM Project B) B
WHERE SubledgerID IS NOT NULL
As I understand the problem, it may be possible that there are projects
that does not map to subledgers and vice versa.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,
Thanks for the improvement and converting the script.
Good luck,
Shervin
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94002C72C286Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > This is my solution. I used your first example (because of its shorter
> > names!)
> > It's funny that we don't need table A at all. All we need is its
> > relations to B and C.
> Very nice solution! It spent some time to convince myself that it
> addresses the general problem and not just produces the desired output
> by chance, so to speak.
> I did find a need for a minor improvement:
> SELECT ProjectID, SubledgerID
> FROM (SELECT ProjectID,
> SubledgerID =
> (SELECT C.SubledgerID
> FROM SubledgerID C
> LEFT JOIN SubledgerProduct AC
> ON C.SubledgerID = AC.SubledgerID
> AND AC.ProductID IN (SELECT AB.ProductID
> FROM ProjectProduct AB
> WHERE AB.ProjectID = B.ProjectID)
> GROUP BY C.SubledgerID
> HAVING COUNT(AC.ProductID) = (SELECT COUNT(*)
> FROM ProjectProduct AB
> WHERE AB.ProjectID = B.ProjectID)
> AND COUNT(AC.ProductID) = (SELECT COUNT(*)
> FROM SubledgerProduct AC
> WHERE AC.SubledgerID =
> C.SubledgerID)
> )
> FROM Project B) B
> WHERE SubledgerID IS NOT NULL
> As I understand the problem, it may be possible that there are projects
> that does not map to subledgers and vice versa.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Wow,
Those are some well worked querys.
Thank you very much for them, they seem to address my situation.
In my specific probelem, there are no Projects that do not map to
Subledgers, but Subledger to Project does not alway match - the
Subeldger table will always map directly to all possible combinations of
Products, but not all possible combinations of Products will map to a
Project.
Simon Withers
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
