Solved

SQL Query select  where field1 = x and field1 = y

Posted on 2013-05-14
9
202 Views
Last Modified: 2013-10-04
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
Comment
Question by:smhorton
9 Comments
 
LVL 2

Expert Comment

by:Psihawk99
ID: 39164982
Can you post the query you are attempting to run?
0
 
LVL 25

Expert Comment

by:Ron M
ID: 39165004
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39165089
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
 

Author Comment

by:smhorton
ID: 39165150
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:smhorton
ID: 39165180
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
 
LVL 31

Expert Comment

by:awking00
ID: 39165299
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
 

Author Comment

by:smhorton
ID: 39165586
The last example just above using adventureworks 2008 doesn't return any rows for me.  Sorry.
0
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
ID: 39165669
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
 
LVL 31

Expert Comment

by:awking00
ID: 39165678
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now