SQL Query: Not leaving out blank entries.

Posted on 2011-04-20
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])
Question by:Anthony_B

    Author Comment

    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:

    New Jersey

    Open in new window

    Currently it is showing

    New Jersey

    Open in new window

    LVL 2

    Expert Comment

    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.
    LVL 2

    Accepted Solution

    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]

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn how the fundamental information of how to create a table.
    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.

    754 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

    16 Experts available now in Live!

    Get 1:1 Help Now