CODEWITHSUNDEEP
sqljoin
sribasu
sribasu

Reputation: 660

SQL Query With Join for 2 Tables

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

Answers (4)

A l w a y s S u n n y
A l w a y s S u n n y

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

sribasu
sribasu

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

Carlo Cruz
Carlo Cruz

Reputation: 41

u might need a Foreign Key for user_id to Notes. and use INNER JOIN

Upvotes: 1

Vinnie
Vinnie

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

Related Questions