MYSQL加入两个具有相同数据的表

I have two tables member and alumni. alumni table can have the same member in more than one row with a different year field. I want to select all the data from both the table. The table given is:

我有两个桌子成员和校友。校友表可以在具有不同年份字段的多行中具有相同的成员。我想从表中选择所有数据。给出的表格是:

Alumni:

 id, regd, name,   status,    year

 1    1    kim     inactive   2013
 2    1    kim     inactive   2014
 3    1    kim     inactive   2015 //This is generated for alumni purpose

The table member given is:

给出的表成员是:

regd, name,   status,    year
 1    kim     Active     2015

The expected output is:

预期的产出是:

`regd`, `name,`   `status,`    `year`
 1       kim       Active       2015 
 1       kim       inactive     2014 
 1       kim       inactive     2013

If there is no record of for example 2015 year in alumni, it will still display the other record in alumni and 2015 from member table. I am trying to display this with regd using the following php and mysql statement but it does not work as expected.

如果在校友中没有例如2015年的记录,它仍将在会员表中显示校友和2015年的其他记录。我试图使用以下php和mysql语句使用regd显示这个,但它不能按预期工作。

Mysql:

SELECT m.*, a.* FROM member m 
LEFT JOIN alumni a ON m.regd = a.regd
WHERE m.regd ='1' GROUP BY a.year ORDER BY a.year DESC;

PHP:

foreach($members as member):
echo $member['regd'].' '.$member['year'].'<br>';
endforeach;

The error is, it selects all data from alumni table only. Where could I go wrong? Though I did not provide fiddle here, I hope this makes my point clear. please help me.

错误是,它仅从校友表中选择所有数据。哪里可能出错?虽然我没有在这里提供小提琴,但我希望这能说明我的观点。请帮我。

1 个解决方案

#1

It is OUTER JOIN not supported by MySQL.

MySQL不支持OUTER JOIN。

https://*.com/a/4796911/4421474

So you should do something like:

所以你应该这样做:

SELECT res.* FROM (
SELECT 
m.regd, 
m.name, 
COALESCE(m.status, a.status),
m.year,
FROM member m 
LEFT JOIN alumni a 
ON m.regd = a.regd
   AND m.year = a.year
WHERE m.regd ='1' 
UNION
SELECT 
a.regd, 
a.name, 
a.status,
a.year,
FROM member m 
RIGHT JOIN alumni a 
ON m.regd = a.regd
   AND m.year = a.year
WHERE a.regd ='1' 
   AND m.regd IS NULL
) res
ORDER BY res.year DESC

#1