SQL select statement help - Microsoft SQL Server 2005 - SQL Syntax

Posted on 2009-12-29
Last Modified: 2012-05-08
Table Name: index

doc_id        index_id     doc_field     value      person_nbr
58B57721  C192173B  8A2E7328  553              2D28D988
58B57721  46A5DB1E  96A237F3  1602      2D28D988
58B57721  CF4946CB  DE2A292E  745              2D28D988
58B57721  446C6068  5AFC40D0  20090601  NULL

I would like to write a select statement on the above tables that will return the value column's record of 20090601 above from table index.

I also need the index.doc_field_id = '5AFC40D0', which I can hard-code, and I know the person_nbr going in, but it is not associated with that particular row (there is a NULL value). I do not know the doc_id going into the query. There are also many records in the table with multiple doc_id's and multiple person_nbr's = the above set is merely a sample.

I basically need to say something like "Where the doc_id's are the same, and the other 3 records have my person_nbr and this one doesnt, and the len(value) = 8, then return that value. The person_nbr not existing is killing me here.  I need help writing the code, if possible, that will give me that value.  

Thoughts?  Thanks!
Question by:robthomas09

    Author Comment

    An additional point of clarification - there are multiple doc_field's of '5AFC40D0' associated with other person_nbr's as well.
    LVL 75

    Accepted Solution

    SELECT *
    FROM yourTable t
    WHERE t.person_nbr IS NULL and LEN(value) = 8 and EXISTS (SELECT 1 FROM yourTable y where y.Doc_id = t.Doc_id and y.person_nbr IS NOT NULL )
    LVL 31

    Assisted Solution

    See attached.

    Author Comment

    Thanks for the responses - I will give em a try and report back soon - thanks again!

    Author Comment

    Thanks all - worked like a charm

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    24 Experts available now in Live!

    Get 1:1 Help Now