CODEWITHSUNDEEP
mysqlsqljoin
Radical_Activity
Radical_Activity

Reputation: 2738

How to SELECT multiple columns with MySQL based on multiple records?

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.

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?

Upvotes: 3

Views: 94

Answers (3)

Linger
Linger

Reputation: 15048

You will have to join to the sample table twice in order to get the information you are after (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

Upvotes: 5

Erik Gillespie
Erik Gillespie

Reputation: 3959

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.

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;

Upvotes: 0

Man Programmer
Man Programmer

Reputation: 5356

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

Upvotes: 0

Related Questions