CODEWITHSUNDEEP
mysql
Jenya Kirmiza
Jenya Kirmiza

Reputation: 711

Select from different tables

I have table 

user_task(_id,email,task_id)

Table

users(_id int, email varchar(50))

Table 

tasks(_id,name)

And table 

subtask(_id,name, task_id)

I need two select statements

  1. Select all tasks of certain user by email
  2. Select subtasks of this user

Don't have experience in sql-joins. so i need some help. thanks in advance

I think this statement will do the job for the 1st statement, but im not sure

SELECT * FROM tasks WHERE _id=
(SELECT task_id FROM user_tasks WHERE email='$email')

Upvotes: 0

Views: 74

Answers (4)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Selecting all the tasks for a certain email id you need to join user_tasks and tasks

select
ut._id,
ut.email,
ut.task_id,
t.name
from user_task ut
join tasks t on t._id = ut.task_id
where ut.email = 'some email id'

Selecting sub-tasks related to the user you need to join all the tables something as

select  
ut._id ,
ut.email,
ut.task_id,
t.name as task_name,
st._id as sub_task_id,
st.name as sub_task_name
from subtask st
join tasks t on t._id = st.task_id
join user_task ut on ut.task_id = t._id
where ut.email = 'some email id'

UPDATE After the question being updated I see that there is user table and you are storing emailid to user_task , I would recommend to store the userid in the table user_task so that if user changes the email id it will not create any issue to the related tables and you just need to join the user table to user_tasks table using the id.

So if you would have stored the user_id in the user_task table instead of email the queries would have looked like

select
u.id as user_id,
u.email,
ut.task_id,
t.name
from user_task ut
join user u on u._id = ut.user_id
join tasks t on t._id = ut.task_id
where u.email = 'some email id'

and

select  
u._id as user_id,
u.email,
ut.task_id,
t.name as task_name,
st._id as sub_task_id,
st.name as sub_task_name
from subtask st
join tasks t on t._id = st.task_id
join user_task ut on ut.task_id = t._id
join user u on u._id = ut.user_id
where u.email = 'some email id'

Upvotes: 2

abhsss96
abhsss96

Reputation: 353

SELECT "tasks".* FROM "tasks" 
INNER JOIN "user_tasks" ON "user_tasks"."task_id" = "tasks"."id" 
INNER JOIN "subtask" ON "subtask"."task_id" = "tasks"."id"

You can change INNER JOIN to OUTER JOIN and manipulate the data sets by using Joins

See here: http://www.tutorialspoint.com/mysql/mysql-using-joins.htm

Upvotes: 1

paularka90
paularka90

Reputation: 23

SELECT * FROM tasks tsk INNER JOIN user_tasks usr_tsk ON tsk._id = usr_tsk.task_id WHERE usr_tsk.email='$email';

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

Try with INNER JOIN

SELECT *
FROM tasks T
INNER JOIN user_tasks UT ON T._id = UT.task_id
WHERE UT.email='$email'

Upvotes: 0

Related Questions