去联盟时过滤重复的结果

This is a PDO Query. I am getting a duplicate result despite using GROUP BY.

The first table, table1, contains a list of entries that contains a group TG.

The second table, table2, links back to a table that lists all of the group TGs associated with the network queried. Sometimes there are duplicate entries in the two tables EXCEPT for an "access" value. All that other SELECT data is the same. I want to filter out all entries from the DMR_Networks table with the same group TG where the access value is different. the value coming from the DMR_repeater_links table wins when there is a conflict.

table3 is the normalized table that lists all of the possible TG's, but the access value remains with table1 and table2.

Here is my query:

$SQL="SELECT table3.`TG`, 
             TS,  
             table3.`assignment`, 
             `access`
        FROM table1
       INNER JOIN table3 on table1.RB_TG_ID = table3.RB_TG_ID
       WHERE `state_ID`=:state_id 
         AND `rpt_ID`=:ID 
       GROUP BY table3.`TG`
       UNION 
      SELECT table3.`TG`, 
             TS, 
             table3.`assignment`, 
             `FP` as `access`
        FROM table2
       INNER JOIN table2 
          ON table2.RB_TG_ID = table3.RB_TG_ID
       WHERE table2.Network = '".$row_network['Network']."'
       GROUP BY table3.`TG` 
       ORDER BY `TS`, `TG`";

WHILE ($row_talk_groups = $link_DMR->fetch(PDO::FETCH_ASSOC)) {

The result returns the duplicate TGs because the access value is different. I can't figure out how to isolate the DISTINCT value on the TG column or how to GROUP BY on the TG column across the entire query.

I tried

WHILE ($row_talk_groups = $link_DMR->fetchAll(PDO::FETCH_GROUP)) {

But, it fails to display any results.

Here is the final query that worked:

$SQL="SELECT unionResult.TG, unionResult.TS, unionResult.assignment, unionResult.access
  FROM
     (SELECT table3.`TG`, `TS`, table3.`assignment`, `access`
      FROM  table1
      INNER JOIN `table3` on table1.RB_TG_ID = table3.RB_TG_ID
      WHERE `state_ID`=:state_id AND `rpt_ID`=:ID
      UNION  SELECT DISTINCT table3.TG, TS, table3.`assignment`, `FP` as `access`
      FROM table2
      INNER JOIN `table3` on DMR_Networks.RB_TG_ID = table3.RB_TG_ID
      WHERE table2.Network = '".$row_network['Network']."')
  AS unionResult GROUP BY `TG`";
查看全部
drt12345678
drt12345678
2017/01/02 19:15
  • php
  • mysql
  • pdo
  • union
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复