MySql Subquerying 2表(MySql Subquerying 2 tables)

MySql Subquerying 2表(MySql Subquerying 2 tables)

我有两个不同的表，一个是匹配，另一个是玩家，我正在尝试运行一个返回name （来自players表）和matchno （来自matches ）的子查询。 我可以让它返回一个或另一个但不是两个。

我的一些查询也返回错误（在工作台6.0中）

错误代码：1242。子查询返回超过1行

具有讽刺意味的是杀了我=）

我在这个问题上的逻辑是尝试使用规范化，即playerno 。

即：

select name from players where playerno in (select matchno from matches group by matchno)

这将返回播放器表中的名称。 我是MySql的新手，并且已经搜索了我的书和互联网，我还在敲打墙头，任何帮助都将不胜感激!!! 提前致谢

我到了使用连接和子查询工作的查询：

select , from (matches as m inner join players as p on m.playerno = p.playerno)

I have 2 different tables one is matches, the other is players, I am trying to run a subquery that returns name (from players table) and matchno (from matches). I can get it to return one or the other but not both.

Also some of my queries return the error (in workbench 6.0)

Error Code: 1242. Subquery returns more than 1 row

The irony is killing me =)

My logic on this one is trying to use the normalization, which is playerno.

:

select name from players where playerno in (select matchno from matches group by matchno)

This returns just the name from the players table. I am pretty new to MySql and have scoured my book and internet and am still banging my head against the wall, any help would be greatly appreciated!!! Thanks in advance

I found the query that worked by using a join and a subquery:

select , from (matches as m inner join players as p on m.playerno = p.playerno)

最满意答案

我认为你必须在查询中加入表。 如果您有这样的事情：

players table id name 1 P1 2 P2 matches table idm idplayer match 1 1 blah 2 1 bleh 2 fly

获得比赛和球员的查询将是

select players.id, , from players, matches where playes.id = matches.idplayer

I think you have to just join the tables in a query. If you have something like this:

players table id name 1 P1 2 P2 matches table idm idplayer match 1 1 blah 2 1 bleh 2 fly

The query to get the matches and players would be

select players.id, , from players, matches where playes.id = matches.idplayer