停止左联接中的数据重复 [英] Stop duplication of data in left join

问题描述

我有一个查询，该查询使用LEFT JOINS从多个表中选择数据.问题是数据正在复制.

I have a query that selects data from several tables using LEFT JOINS. The problem is data is being duplicated.

这是查询

SELECT 
A.ID,
T.T_ID,
T.name,
T.pic,
T.timestamp AS T_ts,
(SELECT COUNT(*) FROM track_plays WHERE T_ID = T.T_ID) AS plays,
(SELECT COUNT(*) FROM track_downloads WHERE T.T_ID) AS downloads,
S.S_ID,
S.status,
S.timestamp AS S_ts,
G.G_ID,                                                
G.gig_name,
G.date_time,
G.lineup,
G.price, 
G.currency,
G.pic AS G_pic,
G.ticket,
G.venue,
G.timestamp AS G_ts

FROM artists A
LEFT JOIN TRACKS T
ON T.ID = A.ID
LEFT JOIN STATUS S
ON S.ID = A.ID
LEFT JOIN GIGS G
ON G.ID = A.ID

WHERE A.ID = '$ID'
ORDER BY S_ts, G_ts AND T_ts DESC LIMIT 20

问题是，如果联接中的一个表具有比另一个表更多的数据，则数据将重复.因此，如果轨道有1行，状态有2行，演出没有行，那么您将从轨道获得的数据就会增加一倍.

The problem is data is duplicated if one of the tables in the join has more data than another. So if tracks has 1 row, status has 2 and gigs has no rows you would get the data from tracks doubled.

我尝试使用GROUP BY A.ID，但这消除了数据.因此，在前面给出的示例中，status show几乎没有一行.

I have tried using GROUP BY A.ID but that eliminates data. So in the example given before there would nly be one row of status show.

我也尝试过GROUP_CONCAT，但是不确定该功能，因此不能告诉您太多信息.

I've also tried GROUP_CONCAT but am unsure on that function so can't tell you much.

使用SELECT DISTINCT具有与GROUP BY A.ID相同的效果.

USING SELECT DISTINCT has the same effect as just the GROUP BY A.ID.

推荐答案

假定艺术家->演出和艺术家->曲目是1-N映射，则有两种选择. (这两个内容都在您的OP的评论中涵盖了

Assuming that artists -> gigs and artists -> tracks are 1-N mappings then you have two choices. (both of which were covered in the comments on your OP

1)指定要返回以获取1-1映射的N行中的哪一行:

1) Specify which of the N rows you want to get back to achieve a 1-1 map:

FROM artists A
LEFT JOIN TRACKS T ON T.ID = A.ID AND T.<SOMETHING> = SOMETHING
LEFT JOIN STATUS S ON S.ID = A.ID
LEFT JOIN GIGS G ON G.ID = A.ID AND G.<SOMETHING> = SOMETHNING

2)在编写时进行连接，并获得多个曲目和演出的条目，然后将其在调用应用程序中旋转.通常，您会在查询中放入ORDER BY子句，并检查相同的演出者密钥并旋转列表.

2) Do the joins as you wrote and get multiple entries for tracks and gigs and then pivot them in your calling application. Generally you'd put an ORDER BY clause in the query and check for the same artist key and pivot the list.

这篇关于停止左联接中的数据重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！