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

  • 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 '16 at 11:03
  • a quite interesting book that is read is 'SQL and Relational Theory, 2nd Edition' – Darii Petru Jun 22 '16 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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