count record if another record does not exist

Posted on 2006-05-03
Last Modified: 2008-02-01
id | siteid | action      |
1  | 50     | TYPE       |
2  | 56     | TYPE       |
3  | 67     | TYPE       |
4  | 50     | CLOSED  |
5  | 56     | CLOSED  |

i have the above table, i want to select the siteid for every record that does not have a 'closed' action associated with it.  for example, with the above table there should only be 1 result...  siteid 67

how can i write a ms sql statement to accomplish this?

Question by:grizum
    LVL 5

    Expert Comment

    >> action associated with it
     what is the association betwwen recods ?
    LVL 17

    Accepted Solution

    Try this
    select siteid,action from yourtable
    where id not in(select id from yourtable where action='CLOSED')
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    SELECT SITEID from urTable t2
    WHERE NOT EXISTS (SELECT 1 FROM urTable T1 WHERE T1.Action = 'Closed' AND T1.SiteId =t2.SiteID)

    Author Comment

    Thank You HuyBD .. i have struggled with this for a week.  That sql statement is pure poetry, lol.

    Worth every point .. thanks again
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Did u try the other Query
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran

    I think the query has to be modified to the following

    select siteid,action from urTab
    where siteid not in(select siteid from urTab where action='CLOSED')
    LVL 17

    Expert Comment

    you're welcome grizum!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    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…
    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.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    760 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