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

join, mysql, sql

Solution

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

Problem

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?

Original source