MySQL - 唯一的行，仅对应于3个表中的一个

The following query pulls data correctly as expected, however the left join with lnk_cat_isrc table and through that to catalogue table, brings back repeated data if there is more than one item in catalogue which has the same isrcs from isrc table:

以下查询按预期正确地提取数据，但是左边连接lnk_cat_isrc表并通过它连接到目录表，如果目录中有多个项目与isrc表具有相同的isrcs，则会返回重复数据：

SELECT 
                isrc.ISRC,
                isrc.Track_Name,
                isrc.ArtistName,
                isrc.TitleVersion,
                isrc.Track_Time,
                `isrc_performer`.`PerformerName` ,
                `performer_category`.`PerformerCategory` ,
                `isrc_performer`.`PerformerRole` ,
                `isrc`.`isrc_ID`,
                `isrc_performer`.`Perf_ID`

        FROM `isrc`

        LEFT JOIN `isrc_performer` ON (isrc.isrc_ID = isrc_performer.isrc_ID)
        LEFT JOIN `performer_category` ON (performer_category.PerfCat_ID = isrc_performer.PerfCat_ID)
        LEFT JOIN `lnk_cat_isrc` ON (lnk_cat_isrc.isrc_ID = isrc.isrc_ID)
        LEFT JOIN `catalogue` ON (catalogue.ID = lnk_cat_isrc.cat_id) 
        ORDER BY   isrc_ID     desc LIMIT 0 , 10
        ";

I cannot use group by on isrc, because the isrc_performer table can have more than one performer to an isrc.

我不能在isrc上使用group by，因为isrc_performer表可以有多个isrc表演者。

So the relations are like this: Few items from catalogue table can have several identical items from isrc table. In turn, each isrc can have more than one entry in isrc_performer table.

所以关系是这样的：目录表中的几个项目可以从isrc表中有几个相同的项目。反过来，每个isrc在isrc_performer表中都可以有多个条目。

What I want is to display all corresponding data from isrc_performer in relation to each isrc, but not repeating it for each item from catalogue table. I also want to display all the rest "empty" isrcs (those which don't have any data in isrc_performer table)

我想要的是显示isrc_performer中与每个isrc相关的所有相应数据，但不为目录表中的每个项重复它。我还想显示所有其余的“空”isrcs（isrc_performer表中没有任何数据的那些）

Can you give me any ideas?

你能给我任何想法吗？

P.S. despite I'm not pulling any data from catalogue table itself, I'm using it to search by a catalogue number, when user defines search criteria for $where_condition variable, hence I need to keep it in the query. i.e. $where_condition = "catalogue.Catalogue LIKE '%test%' OR ISRC LIKE '%test%' OR Track_Name LIKE '%test%' OR ArtistName LIKE '%test%' OR TitleVersion LIKE '%test%' OR PerformerName LIKE '%test%' OR PerformerCategory LIKE '%test%' OR PerformerRole LIKE '%test%'";

附：尽管我没有从目录表中提取任何数据，但是当用户定义$ where_condition变量的搜索条件时，我用它来搜索目录号，因此我需要将它保留在查询中。 ie $ where_condition =“catalogue.Catalogue LIKE'％test％'或ISRC LIKE'％test％'OR Track_Name LIKE'％test％'OR ArtistName LIKE'％test％'OR TitleVersion LIKE'％test％'OR PerformerName LIKE' ％test％'或PerformerCategory LIKE'％test％'或PerformerRole LIKE'％test％'“;

------UPD:

------ UPD：

trying to graphically represent possible variation in these 3 tables relations:

试图以图形方式表示这三个表关系中可能的变化：

cat1 - isrc1 - performer1
       isrc2 - performer1
             - performer2
             - performer3

cat2 - isrc2 - performer1
             - performer2
             - performer3
     - isrc3 - performer2
             - performer4

cat3 - isrc4
     - isrc1 - performer1

UPD (pics added)

UPD（图片添加）

Here are screen prints. As you can see on picture 1 there are 9 rows with same isrc number, however there are 3 repeated performers Jason, David, Paul.

这是丝网印刷品。正如你在图1中看到的，有9行具有相同的isrc编号，但是有3个重复的表演者Jason，David，Paul。

This is because 3 different catalogue items have this exact isrc with 3 different performers as per pic 2

这是因为3个不同的目录项具有这个精确的isrc，具有3个不同的表演者，如图2所示

= 1(isrc) * 3(catalogue) * 3(performers) = 9 row on output

= 1（isrc）* 3（目录）* 3（表演者）= 9行输出

All I want is that Performers grid would only display 3 rows of this isrc for each performer.

我想要的是Performers网格只为每个表演者显示3行这个isrc。

1 个解决方案

#1

---Rearrange the answer to put the "best" option up top.. .but is all of this for naught.. w/o any data from lnk_cat_isrc or catalogue being returned, why does filtering on catalog make a difference? we're returning all isrc regardless of any filtering because it's a left join...

---重新排列答案，将“最佳”选项放在最上面..但是这一切都是徒劳的......没有来自lnk_cat_isrc或目录的任何数据被返回，为什么对目录的过滤有所不同？我们将返回所有isrc而不管任何过滤因为它是左连接...

So this brings into question given sample data what are the expected results.

因此，在给定样本数据的情况下，这会产生什么样的预期结果。

Possibly more elegant... (but not sure if it would be faster) moving away from exists and simply using a distinct in a subquery so catalog queries always return 1 row per isrc; solving the 1-M problem keeping the left join thereby keeping the isrc records not in the catalog limits. Return all isrc information performer information if it exists, performer category info if it exists and catalogue information If, and only if it matches the catalog filters.

可能更优雅......（但不确定它是否会更快）离开存在并简单地在子查询中使用distinct，因此目录查询总是每个isrc返回1行;解决保持左连接的1-M问题，从而保持isrc记录不在目录限制中。返回所有isrc信息执行者信息（如果存在），执行者类别信息（如果存在）和目录信息If，并且仅当它与目录过滤器匹配时。

SELECT isrc.ISRC
    , isrc.Track_Name
    , isrc.ArtistName
    , isrc.TitleVersion
    , isrc.Track_Time
    ,`isrc_performer`.`PerformerName` 
    ,`performer_category`.`PerformerCategory` 
    ,`isrc_performer`.`PerformerRole` 
    ,`isrc`.`isrc_ID`
    ,`isrc_performer`.`Perf_ID`
FROM `isrc`
LEFT JOIN `isrc_performer` 
  ON isrc.isrc_ID = isrc_performer.isrc_ID
LEFT JOIN `performer_category` 
  ON performer_category.PerfCat_ID = isrc_performer.PerfCat_ID
LEFT JOIN (SELECT distinct lnk_cat_isrc.isrc_ID
           FROM `lnk_cat_isrc` 
           INNER JOIN `catalogue` 
             ON catalogue.ID = lnk_cat_isrc.cat_id
           WHERE...) DCat
   ON Dcat.isrc_ID = isrc.isrc_ID
ORDER BY   isrc_ID     desc 
LIMIT 0 , 10;

As you pointed out the join is causing the problem. So eliminate the join and use the exists notation. Distinct would also work since you're not selecting any values from catalog; though exists should be faster.

正如您所指出的那样，连接会导致问题。因此，消除连接并使用存在符号。由于您没有从目录中选择任何值，因此区别也会有效;虽然存在应该更快。

Fast but doesn't include all isrc records... (not sure why the or not exists should bring them back in...)

快但不包括所有isrc记录......（不知道为什么存在或不存在应该将它们带回......）

SELECT isrc.ISRC
     , isrc.Track_Name
     ,isrc.ArtistName
     ,isrc.TitleVersion
     ,isrc.Track_Time
     ,`isrc_performer`.`PerformerName` 
     ,`performer_category`.`PerformerCategory` 
     ,`isrc_performer`.`PerformerRole` 
     ,`isrc`.`isrc_ID`
     ,`isrc_performer`.`Perf_ID`
    FROM `isrc`
    LEFT JOIN `isrc_performer` 
      ON (isrc.isrc_ID = isrc_performer.isrc_ID)
    LEFT JOIN `performer_category` 
      ON (performer_category.PerfCat_ID = isrc_performer.PerfCat_ID)
    WHERE EXISTS (SELECT * 
                  FROM  `lnk_cat_isrc` 
                  INNER JOIN `catalogue` 
                    ON catalogue.ID = lnk_cat_isrc.cat_id
                   --and your other criteria
                  WHERE (lnk_cat_isrc.isrc_ID = isrc.isrc_ID)
                  ) 
     OR NOT EXISTS (SELECT * 
                    FROM `lnk_cat_isrc` 
                    WHERE lnk_cat_isrc.isrc_ID = isrc.isrc_ID
    ORDER BY isrc_ID desc 
    LIMIT 0 , 10

Or using select distinct simple straight forward; but slow

或者使用精选的简单直接;但很慢

 SELECT isrc.ISRC
     , isrc.Track_Name
     ,isrc.ArtistName
     ,isrc.TitleVersion
     ,isrc.Track_Time
     ,`isrc_performer`.`PerformerName` 
     ,`performer_category`.`PerformerCategory` 
     ,`isrc_performer`.`PerformerRole` 
     ,`isrc`.`isrc_ID`
     ,`isrc_performer`.`Perf_ID`
  FROM `isrc`
  LEFT JOIN `isrc_performer` 
    ON (isrc.isrc_ID = isrc_performer.isrc_ID)
  LEFT JOIN `performer_category` 
    ON (performer_category.PerfCat_ID = isrc_performer.PerfCat_ID)
  LEFT JOIN `lnk_cat_isrc` 
    ON (lnk_cat_isrc.isrc_ID = isrc.isrc_ID)
  LEFT JOIN `catalogue` 
    ON (catalogue.ID = lnk_cat_isrc.cat_id) 
   --AND (other criteria on catalog here, cause in a where clause you left joins will behave like inner joins)
  ORDER BY isrc_ID desc 
  LIMIT 0 , 10;