CODEWITHSUNDEEP
c#sql
Chris
Chris

Reputation: 7611

SQL Advice - selecting based upon column in another table

I'm having a bit of headache with some SQL I'm trying to write. Basically, there's 2 tables that have the following fields (only relevant ones to SQL displayed):

Invoices

 accno (fk, linked to id on accounts)

Accounts

 id (pk)
 allocateduser

Basically, my SQL needs to return all invoices for which account has their current logged in name as the AllocatedUser, which is stored in a Session. Is this simple to do?

Thanks

Upvotes: 0

Views: 74

Answers (2)

Peposh
Peposh

Reputation: 182

You can use semi-join:

SELECT * 
  FROM invoice
 WHERE EXISTS(

SELECT 1 
  FROM accounts 
 WHERE allocateduser = @allocateduser_value
   AND pk = fk)

@allocateduser_value is parameter you pass from your session. Beware on case of string, so John is not john ;-)

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You can look up all accounts for a user with a where statement, and link the result to the invoices table with a join. For example:

select  *
from    invoices i
join    accounts a
on      i.accno = a.id
where   a.allocateduser = 'YourUser'

Upvotes: 1

Related Questions