Filtering table dynamically using the user details entered

I Have a User tab开发者_开发问答le, where it has userFirstname, emailid, organisation as columns. I have to filter the users based on the user that have logged into the system. I have the Email ID of the user who logs into the system. I need a sql statement so that the users are filtered based on the organisation which is same as the logged in user.

something like this, (get the logged on users organisation, then find all users in that organisation):

SELECT *
FROM   USER
WHERE  organisation IN (SELECT organisation
                         FROM   USER
                         WHERE  emailid = loggedon_emailid)

I don't think it's a big deal here, because this sounds like a homework question. But, in an application, you will most likely need to frequently reference the data associated with a given emailid. Thus, you would probably want to run

SELECT * FROM User WHERE emailid = loggedon_emailid

at the beginning of your codepath and save that data in memory. Then, to execute the query you need, you could simply run:

SELECT * FROM User WHERE organisation = $loggedon_organisation

Then, when you need to print the user's name or organisation on the screen, you will not need an additional SQL query. And, if you have other tables, you could run more queries without repeatedly looking up the user's row in the User table. For example (this is purely hypothetical):

SELECT organisation_address FROM Organisation WHERE organisation = $loggedon_organisation