[mysql]对多个表查询吗?

标签: MySQL
发布时间: 2015/6/11 21:53:12
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我需要创建一个单一的查询,从三个表在同一数据库中读取。

表:

1. cm_players
player_id
card_no
club_id
surname
name
birthdate

2. cm_clubs
club_id
prefix
name
address
place
stadium_id
telephone
fax
email
web
contact_person
president
secretary
press_officer
status
image

3. cm_registrations
player_id
surname
name
birthdate
birthplace
club_id
date_from
date_to
amateur
status

所以,我需要显示

cm_players.name, 
cm_players.surname, 
cm_clubs.name, 
cm_players.birthdate, 
cm_players.card_no,

加入 cm_players 与 cm_clubs 获得俱乐部的名称,并检查在 cm_registrations 中,如果用户是活跃......

已尝试像:

SELECT cm_players.name, cm_players.surname, cm_clubs.name, cm_players.birthdate, cm_players.card_no, cm_registrations.amateur FROM cm_players 
INNER JOIN cm_clubs on cm_players.club_id = cm_clubs.club_id, cm_registrations 
WHERE cm_players.name LIKE '%$name%' AND cm_players.surname LIKE '%$surname%' AND cm_players.player_id = cm_registrations.player_id AND cm_registrations.amateur = 0 ORDER BY cm_players.player_id ASC

但运气不佳......

谁能帮我打造此查询?

解决方法 1:

在这里我们设置表的别名,和通过使用合适的别名名称的那张桌子获取的列的值,我们使用基于两个表的内部联接俱乐部和登记

SELECT cmp.name, cmp.surname, cmc.name, cmp.birthdate, cmp.card_no, cmr.amateur 
  FROM cm_players cmp 
  INNER JOIN cm_clubs cmc on cmp.club_id = cmc.club_id
  INNER JOIN cm_registrations cmr on cmp.player_id = cmr.player_id
  WHERE cmp.name LIKE '%$name%' 
  AND cmp.surname LIKE '%$surname%' 
  AND cmr.amateur = 0 
  ORDER BY cmp.player_id ASC
赞助商