?
Solved

SQL Query: Not leaving out blank entries.

Posted on 2011-04-20
3
Medium Priority
?
207 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])
0
Comment
Question by:Anthony_B
  • 2
3 Comments
 

Author Comment

by:Anthony_B
ID: 35434450
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

0
 
LVL 2

Expert Comment

by:SQLSergentMike
ID: 35434517
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.
0
 
LVL 2

Accepted Solution

by:
SQLSergentMike earned 2000 total points
ID: 35434638
oops sorry..made a syntax error, left your brackets in.

SELECT DISTINCT StateAbbrev, StateName
FROM RepStates INNER join Contacts ON StateAbbrev=State WHERE Yeswecalled IS NULL ORDER BY [StateName]
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

569 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