I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there is one more column joining year. Now I want to return the data like Name(actual name of user), Department(actual name of the department) and year by using subqueries. I tried but I am not getting any results.
main table
--------------------------
| user | department| year|
--------------------------
| 1 | 1 | 1992|
--------------------------
| 2 | 12 | 2007|
--------------------------
users table
--------------------
| pkey | name |
--------------------
| 1 | someone |
--------------------
| 2 | someName |
--------------------
department table
--------------------
| pkey | name |
--------------------
| 1 | Dep1 |
--------------------
| 12 | Dep2 |
--------------------
expected result from query for main table with subquery for user and department by doing inner joins
--------------------------
| user | department| year|
--------------------------
| someone | Dep1 | 1992|
--------------------------
| someName | Dep2 | 2007|
--------------------------
Best Answer
why you need use subqueries ?
You can use joins for get data
I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1