Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-12-29
Medium Priority
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
  • 3

Author Comment

ID: 26138845
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

Aneesh Retnakaran earned 1600 total points
ID: 26138867
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 32

Assisted Solution

awking00 earned 400 total points
ID: 26139139
See attached.

Author Comment

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

Author Comment

ID: 26141144
Thanks all - worked like a charm

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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.​
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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