php - 将 2 个内部联接结果合并为一个结果

Question

每个人我都有问题。我有两个单独运行良好的查询。但是我想把我试过的一个查询变成一个查询，但是这些都行不通。我需要加入两个输出。

第一个查询:

SELECT q_internal_table.q_part_id, 
   q_external_table.q_external_id, 
   q_external_table.q_external_approve, 
   q_external_table.q_order_id, 
   q_internal_table.q_internal_id, 
   q_internal_table.q_internal_approve 
   FROM   (SELECT parts.id                  AS q_part_id, 
           parts.order_id            AS q_order_id, 
           external_reports.id       AS q_external_id, 
           external_reports.approved AS q_external_approve 
    FROM   parts 
           INNER JOIN external_reports 
                   ON( parts.id = external_reports.part_id )) 
   q_external_table 
   INNER JOIN (SELECT parts.id                  AS q_part_id, 
                      internal_reports.id       AS q_internal_id, 
                      internal_reports.approved AS q_internal_approve 
               FROM   parts 
                      INNER JOIN internal_reports 
                              ON( parts.id = internal_reports.part_id )) 
          q_internal_table 
           ON( q_external_table.q_part_id = q_internal_table.q_part_id ) 
   WHERE  ( q_external_table.q_external_approve = 'Y' 
      OR q_internal_table.q_internal_approve = 'Y' ) 

第二个查询:

SELECT q_five_internal_table.q_five_part_id,
   q_five_internal_table.q_five_internal_id, 
   q_five_internal_table.q_five_internal_approve,
   q_five_external_table.q_five_external_id, 
   q_five_external_table.q_five_external_approve, 
   q_five_external_table.q_five_order_id
   FROM   (SELECT parts.id                  AS q_five_part_id, 
           parts.order_id            AS q_five_order_id, 
           five_way_external_reports.id       AS q_five_external_id, 
           five_way_external_reports.approved AS q_five_external_approve 
    FROM   parts 
           INNER JOIN five_way_external_reports 
                   ON( parts.id = five_way_external_reports.part_id )) 
   q_five_external_table 
   INNER JOIN (SELECT parts.id                  AS q_five_part_id, 
                      five_way_internal_reports.id       AS q_five_internal_id, 
                      five_way_internal_reports.approved AS q_five_internal_approve 
               FROM   parts 
                      INNER JOIN five_way_internal_reports 
                              ON( parts.id = five_way_internal_reports.part_id )) 
          q_five_internal_table 
           ON( q_five_external_table.q_five_part_id = q_five_internal_table.q_five_part_id ) 
  WHERE  ( q_five_external_table.q_five_external_approve = 'Y' 
      OR q_five_internal_table.q_five_internal_approve = 'Y' ) 

第一个查询结果:

第二个查询结果:

我试过这个查询:-

SELECT q_internal_external_table.*, 
   q_five_internal_external_table.* 
FROM   (SELECT q_internal_table.q_part_id, 
           q_external_table.q_external_id, 
           q_external_table.q_external_approve, 
           q_external_table.q_order_id, 
           q_internal_table.q_internal_id, 
           q_internal_table.q_internal_approve 
    FROM  (SELECT parts.id                  AS q_part_id, 
                  parts.order_id            AS q_order_id, 
                  external_reports.id       AS q_external_id, 
                  external_reports.approved AS q_external_approve 
           FROM   parts 
                  INNER JOIN external_reports 
                          ON( parts.id = external_reports.part_id )) 
          q_external_table 
          INNER JOIN (SELECT parts.id                  AS q_part_id, 
                             internal_reports.id       AS q_internal_id, 
                             internal_reports.approved AS q_internal_approve 
                      FROM   parts 
                             INNER JOIN internal_reports 
        ON( parts.id = internal_reports.part_id )) 
        q_internal_table 
       ON( q_external_table.q_part_id = q_internal_table.q_part_id ) 
       WHERE  ( q_external_table.q_external_approve = 'Y' 
       OR q_internal_table.q_internal_approve = 'Y' )) 
       q_internal_external_table 
       INNER JOIN (SELECT q_five_internal_table.q_five_part_id, 
       q_five_internal_table.q_five_internal_id, 
       q_five_internal_table.q_five_internal_approve, 
       q_five_external_table.q_five_order_id, 
       q_five_external_table.q_five_external_id, 
       q_five_external_table.q_five_external_approve 
       FROM   (SELECT parts.id                           AS q_five_part_id, 
       parts.order_id                     AS 
       q_five_order_id 
       , 
      five_way_external_reports.id       AS 
       q_five_external_id, 
       five_way_external_reports.approved AS 
       q_five_external_approve 
       FROM   parts 
       INNER JOIN five_way_external_reports 
       ON( parts.id = five_way_external_reports.part_id )) 
       q_five_external_table 
       INNER JOIN (SELECT parts.id  AS     q_five_part_id, 
       five_way_internal_reports.id       AS 
       q_five_internal_id, 
       five_way_internal_reports.approved AS 
       q_five_internal_approve 
       FROM   parts 
       INNER JOIN five_way_internal_reports 
       ON( parts.id = five_way_internal_reports.part_id )) 
       q_five_internal_table 
       ON ( q_five_external_table.q_five_part_id = 
       q_five_internal_table.q_five_part_id ) 
       WHERE  ( q_five_external_table.q_five_external_approve = 'Y' 
       OR q_five_internal_table.q_five_internal_approve = 'Y' )) 
       q_five_internal_external_table 
       ON ( q_internal_external_table.q_part_id = 
       q_five_internal_external_table.q_five_part_id ) 

查询结果:- 在这个查询中有三个结果。但我希望 7 结果与第一个查询结果一样。这三个答案已经在第一个查询结果中。我也需要休息 4。我认为问题在于 ON q_internal_external_table.q_part_id = q_five_internal_external_table.q_five_part_id。但是不知道怎么解决。

请帮帮我。提前致谢。

Answer 1

你需要的是UNION。

两个查询需要具有相同的金额列和类型。在您的情况下，您需要更改所选列的顺序才能生成 UNION。

应该是这样的:

SELECT q_internal_table.q_part_id, 
   q_external_table.q_external_id, 
   q_external_table.q_external_approve, 
   q_external_table.q_order_id, 
   q_internal_table.q_internal_id, 
   q_internal_table.q_internal_approve 
   FROM   (SELECT parts.id AS q_part_id, 
           parts.order_id AS q_order_id, 
           external_reports.id AS q_external_id, 
           external_reports.approved AS q_external_approve 
    FROM parts 
           INNER JOIN external_reports 
                   ON( parts.id = external_reports.part_id )) 
   q_external_table 
   INNER JOIN (SELECT parts.id AS q_part_id, 
                      internal_reports.id AS q_internal_id, 
                      internal_reports.approved AS q_internal_approve 
               FROM   parts 
                      INNER JOIN internal_reports 
                              ON( parts.id = internal_reports.part_id )) 
          q_internal_table 
           ON( q_external_table.q_part_id = q_internal_table.q_part_id ) 
   WHERE  ( q_external_table.q_external_approve = 'Y' 
      OR q_internal_table.q_internal_approve = 'Y' )

UNION ALL 

SELECT q_five_internal_table.q_five_part_id,
      q_five_external_table.q_five_external_id,
      q_five_external_table.q_five_external_approve, 
      q_five_external_table.q_five_order_id,
   q_five_internal_table.q_five_internal_id, 
   q_five_internal_table.q_five_internal_approve   
   FROM   (SELECT parts.id AS q_five_part_id, 
           parts.order_id AS q_five_order_id, 
           five_way_external_reports.id AS q_five_external_id, 
           five_way_external_reports.approved AS q_five_external_approve 
    FROM   parts 
           INNER JOIN five_way_external_reports 
                   ON( parts.id = five_way_external_reports.part_id )) 
   q_five_external_table 
   INNER JOIN (SELECT parts.id AS q_five_part_id, 
                      five_way_internal_reports.id AS q_five_internal_id, 
                      five_way_internal_reports.approved AS q_five_internal_approve 
               FROM parts 
                      INNER JOIN five_way_internal_reports 
                              ON( parts.id = five_way_internal_reports.part_id )) 
          q_five_internal_table 
           ON( q_five_external_table.q_five_part_id = q_five_internal_table.q_five_part_id ) 
  WHERE  ( q_five_external_table.q_five_external_approve = 'Y' 
      OR q_five_internal_table.q_five_internal_approve = 'Y' ) 

请注意，我已经更改了第二个查询中列的顺序以匹配第一个查询的类型。