• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SQL Query select where field1 = x and field1 = y

I need to be able to select EmployeeID, Employee Name from a table where the same field will have a description Sales or Sales Stocker.   The employee has two entries.   If I use an and i don't get anything returned if I use or i get everything.  I just need the employees that have both.
0
smhorton
Asked:
smhorton
1 Solution
 
Psihawk99Commented:
Can you post the query you are attempting to run?
0
 
Ron MalmsteadInformation Services ManagerCommented:
Try this...

SELECT DISTINCT EmployeeID, Employee_Name from TableName
WHERE Description = 'Sales Stocker' AND Employee_Name IN(Select Employee_Name From TableName WHERE Description = 'Sales')
0
 
Scott PletcherSenior DBACommented:
SELECT
    EmployeeID, Employee_Name
FROM dbo.tablename
GROUP BY EmployeeID, Employee_Name
HAVING
    MAX(CASE WHEN Description = 'Sales' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Description = 'Sales Stocker' THEN 1 ELSE 0 END) = 1
ORDER BY
    EmployeeID, Employee_Name
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
smhortonAuthor Commented:
I will post the exact query I am using but it will have to be this afternoon.  Sorry.  I don't have access to that machine from where I am at.  Will also try both of the examples above then as well.  Thanks!
0
 
smhortonAuthor Commented:
SELECT [employee_id]
      ,[first_name]
      ,[last_name]
      ,[dept_no]
      ,[salary]
  FROM [AdventureWorks2008].[dbo].[employee]
  where dept_no = '10' and dept_no ='20'

Above is an example I have made up using adventureworks 2008.  the and between the dept_no returns nothing but I have employees setup with both a 10 and a 20 dept number.
0
 
awking00Commented:
SELECT [employee_id]
      ,[first_name]
      ,[last_name]
      ,[dept_no]
      ,[salary]
      ,COUNT(*)
FROM [AdventureWorks2008].[dbo].[employee]
WHERE dept_no IN ('10','20')
GROUP BY [employee_id],[first_name],[last_name],[dept_no],[salary]
HAVING COUNT(*) = 2;
0
 
smhortonAuthor Commented:
The last example just above using adventureworks 2008 doesn't return any rows for me.  Sorry.
0
 
awking00Commented:
What does the query without the having clause produce?
SELECT [employee_id]
      ,[first_name]
      ,[last_name]
      ,[dept_no]
      ,[salary]
      ,COUNT(*)
FROM [AdventureWorks2008].[dbo].[employee]
WHERE dept_no IN ('10','20')
GROUP BY [employee_id],[first_name],[last_name],[dept_no],[salary]
ORDER BY [employee_id],[first_name],[last_name],[dept_no],[salary];
0
 
awking00Commented:
If dept_no is a numeric datatype, adventureworks might not like doing an implicit conversion. Try removing the quotes -
where dept_no in (10,20)
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now