mysql - 如何返回第一个表中的所有列，而仅返回第二个表中的 1 列

Question

我有一个名为“purchases”的第一个表

news_series, transaction_id, owner_id, amount

我有另一个名为“Events”的表

news_name, news_id, series_id, news_description

我遇到的问题是，如果我这样做

purchases.news_series joins to events.series_id

问题是该系列 ID 可能有多个事件......

我只需要加入一个即可从加入的表中获取news_name，因此基本选择为

Select * from purchases where owner_id=29

140, asldkfj_sdfx34, 29, 40

然后我添加连接的表

Select * 
from purchases 
LEFT JOIN events on purchases.news_series=events.series_id 
where owner_id=29

140, asldkfj_sdfx34, 29, 40,"THIS EVENT", 606, 140, "MY FIRST EVENT"
140, asldkfj_sdfx34, 29, 40,"THIS EVENT", 607, 140, "MY FIRST EVENT"

我最终返回了几行...我只需要一行来从事件表中捕获 new_name 。

Answer 1

I just need one to capture the news_name from the events table.

这就是我要做的:

采购表:

+-------------+----------------+----------+--------+
| news_series | transaction_id | owner_id | amount |
+-------------+----------------+----------+--------+
| 140         | asldkfj_sdfx34 | 29       | 40     |
+-------------+----------------+----------+--------+

事件表:

+------------+---------+-----------+------------------+
| news_name  | news_id | series_id | news_description |
+------------+---------+-----------+------------------+
| THIS EVENT | 606     | 140       | MY FIRST EVENT   |
+------------+---------+-----------+------------------+
| THIS EVENT | 607     | 140       | MY FIRST EVENT   |
+------------+---------+-----------+------------------+

<小时/>

SELECT DISTINCT 仅从连接表中选择您想要的一列:

SELECT DISTINCT p.*, e.news_name
FROM Purchases p
LEFT JOIN Events e ON p.news_series = e.series_id
WHERE p.owner_id = 29

<小时/>

如果您不SELECT DISTINCT，这就是您获得两行的原因。

<小时/>

测试:

;WITH Purchases (news_series, transaction_id, owner_id, amount) AS (
    SELECT '140','asldkfj_sdfx34','29','40'
), Events (news_name,news_id,series_id,news_description) AS (
    SELECT 'THIS EVENT','606','140','MY FIRST EVENT' UNION ALL
    SELECT 'THIS EVENT','607','140','MY FIRST EVENT' )

SELECT DISTINCT p.*, e.news_name
FROM Purchases p
LEFT JOIN Events e ON p.news_series = e.series_id
WHERE p.owner_id = 29