Hey all. Love the product and these forums. This is not about LLBL Gen (yet hehe), but I have been unsuccesful in finding an answer for this problem in other places, so I figured I'd try my luck here...

I'm having trouble finding an elegant (and easily queryable) solution for the following requirement...

There is a "Company" table. I need a way to represent the "collaborations" of these Companies. Meaning Comapny A can be collaborating with companies B and C, but the relation should work both ways in that if I lookup company C then I should get companies A and B. In other words, no ONE company owns the collaboration.

any help would be appriciated. Jason slackboy13013@yahoo.com

slackboy13013 wrote:

Hey all. Love the product and these forums. This is not about LLBL Gen (yet hehe), but I have been unsuccesful in finding an answer for this problem in other places, so I figured I'd try my luck here...

I'm having trouble finding an elegant (and easily queryable) solution for the following requirement...

There is a "Company" table. I need a way to represent the "collaborations" of these Companies. Meaning Comapny A can be collaborating with companies B and C, but the relation should work both ways in that if I lookup company C then I should get companies A and B. In other words, no ONE company owns the collaboration.

any help would be appriciated. Jason slackboy13013@yahoo.com

How about:

--Company Table
CompanyID int identity
Name varchar(20)

--Collaboration Table
CollaborationID int identity
PartnerAID int FK to Company.CompanyID
PartnerBID int FK to Company.CompanyID

--Query getting all partners to Company '1'

SELECT DISTINCT Company.* 
FROM Collaboration
JOIN Company ON Collaboration.PartnerAID = 1 OR Collaboration.PartnerBID = 1

The problem with this structure is that the query will always return the company being queried on as a partner - a result of the "no company is the owner" requirement. This could be filtered in code after retrieval is desired, but maybe there's a better way...

Jeff...

Why not create a collaboration entity table and for each company that is in the collaboration create a collaboration entity record?

CollaborationId, Descr 1, Some Collaborative Event 2, Some Other Collaborative Event

ComanyId, CompanyName 1, CompanyA 2, CompanyB 3, CompanyC

CollaborationEvents CollaborationEventId, CollaborationId, CompanyId 1,1,1 2,1,2 3,1,3 4,2,3 5,2,2

Then select all company entitys that are associated with the Collaboration Id = 1 and you get companies A,B,C back.

If you want to find all companies that Company C has associated with via collaboration you could do this as well because in the data above, company C has collaborated in events 1 and 2. So you could loosely associate at some point C has collaborated with both A and B.

Hope this helps.

Thanks for the input. This is what I have come up with so far, but I'm not really sure how to query it to get what I need. I will supply a company(ID) and need to find all the companies that are collaborating with it.

CREATE TABLE [Collaboration] (
    [CollaborationID] [int] NULL ,
    [Description] [varchar] (1024) NULL 
) 

CREATE TABLE [Company] (
    [CompanyID] [int] NULL ,
    [CompanyName] [varchar] (255) NULL
)

CREATE TABLE [CompanyCollaboration] (
    [CompanyCollaborationID] [int] NULL ,
    [CollaborationID] [int] NULL ,
    [CompanyID] [int] NULL  
)

Im sure there is a more efficient SQL query, but here's a quick stab at it.

Here's the tables you mentioned above, except a primary key added to the 'CompanyCollaboration' table. The other 2 tables have the keys defined as well.
Here's the table definitions..

CREATE TABLE [dbo].[Collaboration] (
    [CollaborationID] [int] NOT NULL ,
    [Description] [varchar] (1024) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company] (
    [CompanyID] [int] NOT NULL ,
    [CompanyName] [varchar] (255) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanyCollaboration] (
    [CompanyCollaborationID] [int] NOT NULL ,
    [CollaborationID] [int] NULL ,
    [CompanyID] [int] NULL 
) ON [PRIMARY]
GO

Here's a query that will return all companies that have collaborated with a given company.

DECLARE @CompanyID int
SET @CompanyID = 3  -- This is the company for which you want to find what other companies were collaborated with

SELECT DISTINCT C.CompanyName FROM Company C 
    JOIN CompanyCollaboration CL ON CL.CompanyID = C.CompanyID
WHERE CL.CollaborationID IN 
    (Select DISTINCT CC.CollaborationID From Company C 
        JOIN CompanyCollaboration CC ON CC.CompanyID = C.CompanyID
        JOIN collaboration CL ON CC.CollaborationID = CL.CollaborationID
    WHERE CC.CompanyID = @CompanyID)
AND C.CompanyID <> @CompanyID

Hope that helps

Jim