查询多对多关系数据库表

Question

我有数据列表和下面的表格。
数据：{ABC111，ABC112，ABC113,111111,111112,111113}
表：

Column 1 Column 2 
ABC111 ABC115 
ABC115 111333 
111111 ABC112 
111111 111112 
ABC123 111113 
111113 ABC113 

我的结果应该是像下面
结果：{ABC115 ，111333,111113，ABC113，ABC123，ABC112,111112}

说明：
数据 - ABC111与ABC115相关联，ABC115也与111333相关联。因此结果是ABC115,111333。 类似地，ABC113与关联到ABC123的111113相关联。 结果 = 111113，ABC123。

是否可以使用查询来实现上述操作。寻找准备好的陈述，我将通过上面提到的数据。

Answer 1

试试这个： -

/*CREATING RECORDS FOR THE MAIN TABLE*/ 

CREATE TABLE TABLES(Column1 text, Column2 text); 

INSERT INTO TABLES VALUES('ABC111','ABC115'); 
INSERT INTO TABLES VALUES('ABC115','111333'); 
INSERT INTO TABLES VALUES('111111','ABC112'); 
INSERT INTO TABLES VALUES('111111','111112'); 
INSERT INTO TABLES VALUES('ABC123','111113'); 
INSERT INTO TABLES VALUES('111113','ABC113'); 
COMMIT; 


/*CREATE LIST TO BE SUPPLIED*/ 

CREATE TABLE LIST(Column1 text); 

INSERT INTO LIST VALUES('ABC111'); 
INSERT INTO LIST VALUES('ABC112'); 
INSERT INTO LIST VALUES('ABC113'); 
INSERT INTO LIST VALUES('111111'); 
INSERT INTO LIST VALUES('111112'); 
INSERT INTO LIST VALUES('111113'); 
COMMIT; 


/*CODE TO GET THE RESULT*/ 

SELECT CONCAT('{',GROUP_CONCAT(RESULT),'}') AS RESULT FROM 
(
SELECT DISTINCT COLUMN1 AS RESULT FROM 
    (
      Select a.column2 as column1,b.column2 
      from 
      (
      Select * from 
      tables 
      where column1 in (SELECT COLUMN1 FROM LIST) 
      ) a 
      inner join 
      tables b 
      on a.column2=b.column1 

      UNION ALL 

      Select b.column2 as column1,b.column1 as Column2 
      from 
      (
      Select * from 
      tables a 
      where column2 in (SELECT COLUMN1 FROM LIST) 
      ) a 
      inner join 
      tables b 
      on a.column1=b.column2 
) a 

UNION ALL 

SELECT DISTINCT COLUMN2 AS RESULT FROM 
(
      Select a.column2 as column1,b.column2 
      from 
      (
      Select * from 
      tables 
      where column1 in (SELECT COLUMN1 FROM LIST) 
      ) a 
      inner join 
      tables b 
      on a.column2=b.column1 

      UNION ALL 

      Select b.column2 as column1,b.column1 as Column2 
      from 
      (
      Select * from 
      tables a 
      where column2 in (SELECT COLUMN1 FROM LIST) 
      ) a 
      inner join 
      tables b 
      on a.column1=b.column2 
) a 

UNION ALL 

SELECT DISTINCT COLUMN2 AS RESULT FROM 
(

     Select distinct a.Column1, a.column2 
     from 
     (
     Select * from 
     tables a 
     where column2 in (SELECT COLUMN1 FROM LIST) 
     ) a 
     inner join 
     (
     Select * from 
     tables a 
     where column2 in (SELECT COLUMN1 FROM LIST) 
     ) b 
     on a.column1=b.column1 
     where a.column2 not in 
    (
    Select column1 from 
     (
      Select b.column2 as column1,b.column1 as Column2 
      from 
      (
      Select * from 
      tables a 
      where column2 in (SELECT COLUMN1 FROM LIST) 
      ) a 
      inner join 
      tables b 
      on a.column1=b.column2 
     ) a 
    )  
) a 
) a; 

希望这有助于:-)