We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Query: Not leaving out blank entries.

Medium Priority
217 Views
Last Modified: 2012-05-11
 I am trying to get a listing of states where we have people that need to be called.  Once all the people in the state are called, I want the state to be removed from the listing of states.  I currently have this statement:

 
SELECT DISTINCT StateAbbrev, StateName FROM RepStates WHERE (StateAbbrev IN (SELECT State FROM Contacts WHERE (Yeswecalled IS NULL))) ORDER BY [StateName]

Open in new window


This will give me all of the states that are in the database, even if there is noone in the state to call.  My thinking was to check CONTACTS to see who still needs to be called, and populate the dropdown list with the list of states that is created, using DISTINCT to make sure that any state is represented only once.  Can anyone help me fine tune this?

RepStates has 2 fields only, StateAbbrev and StateName.  
Contacts has the names and addresses, along with wether they have been called or not (the field [Yeswecalled])
Comment
Watch Question

Author

Commented:
BTW, just want to give an example.  If Pennsylvania has 3 people to call, New Jersey has 1 person to call, Texas has noone to call, and California has 5 people to call, I want the dropdown list to show:

California
New Jersey
Pensylvania

Open in new window


Currently it is showing

California
New Jersey
Pensylvania
Texas

Open in new window

This will give you only the states that need to be called:
SELECT DISTINCT StateAbbrev, StateName
FROM RepStates INNER join Contacts ON StateAbbrev=State WHERE (Yeswecalled IS NULL))) ORDER BY [StateName]

If you wanted the people just add the contact fields to the select.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.