如何基于多个记录使用MySQL选择多个列?

I have 2 tables right now and they look like the following:

我现在有两张桌子，看起来是这样的:

The points table looks like the following:

点表如下所示:

id  country     points
1   4           1
2   7           5
3   8           4

The sample table looks like the following:

样本表如下所示:

id   iso        pts
1    UK         100
2    US         300
3    AU         700
4    BO         1200
5    BA         1500
6    BR         2000
7    HR         5000
8    TD         10000
9    CA         15000

What I basically want is to select ALL data from the points table where the points.country and points.points corresponds to the sample.iso and sample.pts.

我主要想要的是从点表中选择所有的数据。国家和点。点对应于样本。iso和sample.pts。

So the result I'd like to achieve is:

所以我想要达到的结果是:

id  country     points
1   BO          100
2   HR          1500
3   TD          1200

Is this actually achievable? If yes, how?

这是真正可以实现的吗?如果是,如何?

3 个解决方案

#1

You will have to join to the sample table twice in order to get the information you are after (SQL FIDDLE):

您将不得不连接到示例表两次，以获取您需要的信息(SQL FIDDLE):

SELECT p.id, s1.iso AS country, s2.pts AS points
FROM points p
INNER JOIN sample s1 ON p.country = s1.id
INNER JOIN sample s2 ON p.points = s2.id

#2

with the help of left join or any join you can do it

在左连接或任何连接的帮助下，您可以这样做

select t1.id,t1.country,t1.points from table1 as t1 left join table2 as t2 on t1.id=t2.country

#3

This sounds like what you're looking for. It's an inner join between points and sample that matches points.country to sample.id and points.points to sample.pts.

这听起来像是你要找的。它是点和样本之间的内部连接。样的国家。id和点。指向sample.pts。

The data you supplied doesn't show any points.points that match up to sample.pts but I think this is what you're were shooting for. If not, please clarify in your question.

您提供的数据没有显示任何点。得分和三分球一样，但是我认为这就是你的目标。如果没有，请澄清你的问题。

select p.id, s.iso, s.pts
  from points p, sample s
 where p.country = s.id
   and p.points = s.pts;

#1