Reputation: 660
I'm unable to form a query with the following tables, which will find out all the Notes from Note table, which is created by any user, who belongs to the logged in user's same company.
Note
:
note_id (int),
note_text (varchar),
created_by (int)
User
:
user_id (int),
company_id (int)
Logged in user's user id is passed as parameter to the query.
I want to pick up notes from the table Notes
where created_by in (user_id of all users whose company_id = company_id of LOGGED_IN_USER)
Please help me to formulate out the query. Looks pretty straight forward, but just can't reach to it's end.
Upvotes: 1
Views: 72
Reputation: 38552
If i am not misunderstood your question,Try this way
SELECT note_text
FROM Note n
INNER JOIN User u ON u.user_id = n.created_by
WHERE n.created_by= (select u.user_id from User where company_id=LOGGED_IN_USER LIMIT 1 )
Upvotes: 0
Reputation: 660
Thanks Vinnie and all for your responses. I finally succeeded to figure out the query. LOGGED_IN_USER_ID is just a numeric parameter which should be passed to the query.
select n.* from Note n where
n.created_by in (
select u1.user_id from User u1 inner join User u2
on u1.company_id=u2.company_id and u2.user_id = :LOGGED_IN_USER_ID*
)
Thanks again.
Upvotes: 1
Reputation: 41
u might need a Foreign Key for user_id to Notes. and use INNER JOIN
Upvotes: 1
Reputation: 3929
I'm not sure if LOGGED_IN_USER is a table or another object, but if it is a table with the columns you referenced, a join like this would work.
select note_text
from Note n
JOIN User u ON u.user_id = n.created_by
JOIN LOGGED_IN_USER lin ON lin.user_id = u.user_id
and lin.company_id = u.company_id
Upvotes: 3