Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select based on value in another table

Author  Topic 

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-11-27 : 10:07:12
Hi There,

Can you help please.

I need to get all the employees from the organisation table where their role = a value in the settings table.

This is my select statement at the moment which returns no records. It should return at least 15.


Select
OrgTable.Title,
OrgTable.FirstName,
OrgTable.LastName,
OrgTable.Role
From
OrgTable, SettingsTable
Where
SettingsTable.Description = 'AccountsDepartment' AND
CAST(SettingsTable.Value AS INT) = OrgTable.Role

Thanks for your help.

Best Regards,


Always Learning.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 10:24:23
Can you post a few rows from the source tables that should appear in the output?
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-27 : 10:43:01
[code]
SELECT O.*
FROM OrgTable O
JOIN SettingsTable S
ON O.[Role] = S.Value
AND S.[Description] = 'AccountsDepartment';
--or

SELECT *
FROM OrgTable O
WHERE EXISTS
(
SELECT 1
FROM SettingsTable S
WHERE O.[Role] = S.Value
AND S.[Description] = 'AccountsDepartment'
);

-- etc
[/code]
Go to Top of Page

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-11-27 : 10:53:31
Hi There,

Thanks for the reply.

OrgTable
Title, FirstName, LastName, Role
Mr, David, Harris, 14
Mr, Simon, Marshall, 2
Ms, Diane, Elliot, 14
Mr, George, Bentley, 16

SettingsTable
Name, Description, Value
Accounts, AccountsDepartment, 14
Sales, SalesDepartment, 16
Transport, LogisticsDepartment, 2

The Role field in the OrgTable is of type bigint
The value field in the settings table is of type VarChar

Thanks again for your help

Best Regards,

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 11:27:34
using your data and original query I get:


Title FirstName LastName Role
Mr David Harris 14
Ms Diane Elliot 14


Is that correct with your data?
Go to Top of Page

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-11-27 : 11:46:27
Hi gbritton,

Yes that is correct but it's not working for me.

All the best,
Go to Top of Page

Steve2106
Posting Yak Master

183 Posts

Posted - 2014-11-27 : 11:48:35
Hi Ifor,

Your code worked perfectly.

All working now, Thank You.

Do you use something to build your queries or do you do it from memory?

Best Regards,
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 12:35:07
mostly from memory
Go to Top of Page
   

- Advertisement -