0

Hey guys quick question. If i want to make a stored procedure to grab all information from 1 table from a different table column. More detail..... table1 = users PK=accountid table2 = Account PK = accountid The row i want to check is called role(int only contains 1 and 0). so if role = 1 i want to check which accounts have role 1 and display all the users with that role number. if not 1 then 0 will display the other users??

Now i was thinking along the lines of

 USE [database]
 GO

 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE [dbo].[sp_Users_SelectAllByaccountRole]
 (
 @role int
 )
 AS

 BEGIN
 select * from Users 
 where (role = @role from Accounts) 
 && 
 (Users.accountid == account.accountid)

 END

But i do not know the syntax and i aint sure on my logic any help would be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1778625
  • 131
  • 9

1 Answers1

1

Assuming the Role column is on the Accounts table, then it seems like a simple INNER JOIN will do...

SELECT u.*
FROM Users u
INNER JOIN Accounts a on a.AccountID = u.AccountID
WHERE a.Role = @role
davmos
  • 9,324
  • 4
  • 40
  • 43
  • Thankyou very much works well and like a charm after doing this i found a mistake somewhere else but that is my C# side :(. – user1778625 Jun 08 '13 at 07:58
  • You're welcome :) thanks for the question. Also see [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) for more info on joins. – davmos Jun 08 '13 at 08:02