mysql，查询结果合并

Question

表A的结构

表B的结构

已知条件ball_team_id, competition_id
我想要的结果是

1. select * from table_b where ball_team_id=8

2. select is_get_card from table_a where ball_team_id=8 and competition_id=12

1和2的结果合并到一起，类似于下图。

Answer 1

select a.*,b.is_get_card 
from a, b
where a.ball_team_id = b.ball_team_id 
and a.ball_team_id = 8 
and a.competition_id=12

Answer 2

select a.*,b.is_get_card
from a
inner join b on a.ball_team_id = b.ball_team_id
where
a.ball_team_id = 8
a.competition_id=12

Answer 3

SELECT b.*,a.is_get_card 
FROM table_b b 
LEFT JOIN table_a a 
ON b.ball_team_id=a.ball_team_id 
WHERE b.ball_team_id = 8 
AND a.competition_id=12

Answer 4

SELECT a., b.
FROM a
INNER JOIN b
ON a. ball_team_id = b. ball_team_id
WHERE b.ball_team_id = 8
AND a.competition_id=12

Answer 5

根据给的数据样例，取is_get_card的逻辑不严密，如：
select is_get_card from table_a where ball_team_id=8 and competition_id=12
上面的语句返回两行，is_get_card的值分别为为0和1，应该按那个结果？

如按照is_get_card字段的最大值，可以使用下面的sql语句：

select b.*, a1.is_get_card
from b left join (
  select max(is_get_card) as is_get_card
  from a
  where a.competition_id=12
  group by a.ball_team_id
) a1 on b.ball_team_id = a1.ball_team_id

Answer 6

select a.is_get_card,b.* from table_b b left join table_a a on a.ball_team_id = b.ball_team_id where a.ball_team_id=8 and a.competition_id=12