1

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|
--------------------------
1
  • Please show the code you've tried to help others to help you.
    – Serg
    Jun 21, 2016 at 7:10

1 Answer 1

0

why you need use subqueries ?

You can use joins for get data

SELECT u.name AS user, d.name AS departement, m.year
FROM main AS m
INNER JOIN department AS d ON m.departement_id = d.id
INNER JOIN users AS u ON m.user_id = u.id
;

I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1

2
  • Thanks a Lot man!! That worked like magic!!! Can you please suggest me a book so that i can improve on queries in MySQL ???
    – Rohan
    Jun 21, 2016 at 11:03
  • a quite interesting book that is read is 'SQL and Relational Theory, 2nd Edition' Jun 22, 2016 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.