CODEWITHSUNDEEP
sqlselectmappingselection
George Pap
George Pap

Reputation: 25

SQL Select values associated with keys from other table

I have a mapping table tableA

key   value
----------- 
1     "John"
2     "George"
3     "Kate"
4     "loves"
5     "hates"

and another tableB that contains rows based on keys of tableA

col1   col2   col3
------------------
1      5      2
2      4      3
3      4      1

I want to write a selection query which will return rows from table B but replaced with their appropriate values.

e.g. the output has to be:

John   | hates | George
George | loves | Kate
Kate   | loves | John

Thank you.

Upvotes: 1

Views: 179

Answers (2)

dinocarl
dinocarl

Reputation: 728

You should probably put the last 2 items, 'loves' and 'hates', into a separate table as they represent a different type of data than the other 3.

Here are the tables:

users:

id  name
----------
 1  John
 2  Edward
 3  Kate

feelings:

id  type
----------
 1  love
 2  hate

feelings_users:

id  user1_id  feeling_id  user2_id
-----------------------------------
 1      1         2          2
 2      2         1          3
 3      3         1          1

and here's the query:

select `Ua`.`name`, `F`.`type`, `Ub`.`name` from `feelings_users` as `X`
left join `users` as `Ua` on `X`.`user1_id` = `Ua`.`id`
left join `feelings` as `F` on `X`.`feeling_id` = `F`.`id`
left join `users` as `Ub` on `X`.`user2_id` = `Ub`.`id`

and it will output:

name    type  name
--------------------
John    hate  Edward
Edward  love  Kate
Kate    love  John

Upvotes: 1

JeffS
JeffS

Reputation: 2737

SELECT A1.value, A2.value, A3.value 
FROM tableB 
JOIN tableA as A1 ON tableB.col1 = A1.key 
JOIN tableA as A2 ON tableB.col2 = A2.key 
JOIN tableA as A3 ON tableB.col3 = A3.key;

Upvotes: 2

Related Questions