SQL从Parent获取记录，其中Parent Table具有Relation并且Common Item With Other parent

I have three tables Company , Vehicles, Relationships
In company Table there are two columns one is Company ID and the second is Company name and data is like this

我有三个表公司,车辆,关系在公司表中有两列,一列是公司ID,第二列是公司名称,数据是这样的

ID               CompanyName

101              CompanyOne
102              companyTwo
103              CompanyThree

In Vehicles tables there are three columns

在车辆表中有三列

    ID                          CompnayID                                     Vehicle 
   001                            101-                                          BUS
   002                            102                                           BUS
   003                            103                                          Truck 
   004                            103                                          Cycle 
   005                            104                                           Bike
   006                            102                                          Truck
   007                            101                                          truck

In Table Relations there are 4 columns

在表关系中有4列

 RelationshipID    CompanyOneID       Relationship Name    CompanyTwoID
  1                     101            Collaborate              102
  2                     102            Collaborate              101
  3                     104            Collaborate              102
  4                     103            Collaborate              104

Now what I want is, To have all those companies name and Vehicles Name , Where Companies have “Collaboration” Relationship (There may be other types of relationships too) and have Common Vehicles Names , I also want to have single record like Company 101 have Relationship with 102 and have Common Vehicles and similarly company 102 also have been mention in the relation table to have relationship with the company 101 , here I only Want One Match Either Company 101 To company 102 or Company 102 to company 101 .

现在我想要的是,要拥有所有这些公司的名称和车辆名称,公司有“协作”关系(也可能有其他类型的关系)并且拥有通用车辆名称,我也希望拥有像公司101这样的单一记录在关联表中已经提到与102和普通车辆以及类似公司102的关系以与公司101有关系,这里我只希望一个匹配公司101到公司102或公司102到公司101。

e.g Result will be

例如,结果将是

CompanyOne                  CompanyTwo                 Vehicle 
101                          102                        BUS
101                          102                        Truck

Note:- Company 104 have collaboration with some other Companies but they don’t have common Vehicles And Company 103 have common vehicles but they don’t collaborate with the same company with whom it have collaboration that why I don’t want to select it.

注意: - 公司104与其他一些公司合作,但他们没有共同的车辆和公司103有共同的车辆,但他们不与合作的公司合作,为什么我不想选择它。

I am using SQL SERVER

我正在使用SQL SERVER

1 个解决方案

#1

A simple query like below should do what you want:

像下面这样的简单查询应该做你想要的:

SELECT CompanyOneID, CompanyTwoId, V1.Vehicle
FROM Relationsships
INNER JOIN Vehicles V1 ON V1.CompanyId = CompanyOneID
INNER JOIN Vehicles V2 ON V2.CompanyId = CompanyTwoID
WHERE [Relationsship Name] = 'Collaborate' AND V1.Vehicle = V2.Vehicle

If company 1 is related to company 2 and vice versa, and you only want to show each relation once, you will need something like this:

如果公司1与公司2相关,反之亦然,并且您只想显示每个关系一次,您将需要这样的事情:

SELECT DISTINCT
    CASE WHEN CompanyOneID < CompanyTwoID THEN CompanyOneID ELSE CompanyTwoID END,
    CASE WHEN CompanyOneID > CompanyTwoID THEN CompanyOneID ELSE CompanyTwoID END,
    V1.Vehicle
FROM . . .    /* Rest of the query exactly the same as above */

#1