Select based on value in another table

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?

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]

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,

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?

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,

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,

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 12:35:07

mostly from memory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources