Returning unique results

[gk17]

I'm trying to run separate SQL queries to do the following in SQL Server Studio 2005:

- Return rows where the M# has no value for the same person. In this example, it would be Test, David since all the others have one or more M#.
- Return rows the M# begins with the number 2. This would return Test, Frank and Test, Mary.

The biggest issue for me is getting the query to consider all the possible M# and D# scenarios and treat it as a single row (single person).

Please see attached for sample data.

Any help is greatly appreciated.

Thanks.

Kevin.

 

[gmmastros]

There will be several ways to accomplish this.

- Return rows where the M# has no value for the same person. In this example, it would be Test, David since all the others have one or more M#.

It is relatively easy to get all the ID's where there are no M values.

         Select   ID
         From     YourTable
         Group By ID
         Having Count(M) = 0

Note that COUNT(ColumnName) will only count rows where the value is not null. If you have empty strings your table, then you'll need to change this to: [!]Having Count(NullIf(M, '')) = 0[/!]

The trick is to use the query above to return all of the data about the row you care about. For this, you can use a technique called derived table. Think of it this way, if the results of the above query were stored in a table, we could simply join from the table above to the real table. It would be a bad idea to create this table because it would be easy for the data to get out of synch. However, we can write a query that effectively does the same thing (without actually creating a table).

Like this:

Select *
From   YourTable
       Inner Join (
         [!]Select   ID
         From     YourTable
         Group By ID
         Having Count(M) = 0[/!]
		 ) As No_M_Value
		 On YourTable.ID = No_M_Value.ID

Notice how the part in red is exactly the same as the first query. This becomes the derived table. Also notice that it is wrapped in parenthesis and has an alias No_M_Value.

For the second part, you should be able to do:

Select *
From   YourTable
Where  M Like '2%'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Thanks for the reply George. This definitely helped a lot.

I had another column (Site Code) which returned multiple results. Each person can have multiple site codes. I just changed the SELECT * to SELECT DISTINCT ID, etc. to pull the columns. I hope that was the correct way to do this.

I'm kind of familiar with joining tables but if I want to use it in this instance, what is the proper way to do this? I thought I had everything in one table before. I actually need two tables joined to get this to work. Here are the details:

Table1 - contains the person's ID (unique key), last name and first name and DOB
Table2 - contains the ID (unique key), M# and D#

 

[gk17]

One update and I apologize for this one. I overlooked one item. I was looking through the results returned and still see some duplicates. Some of my results have more than one D# (M# is the same but is excluded in this query so doesn't matter). This returned the same person name twice. They have the same ID so they should only show up once.

Thanks.

 

[gmmastros]

You kinda lost me. Can you post your query and some sample data from both tables and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

I didn't pull all the fields but the main ones are listed below:

vusrPerson contains:
- ID
- FirstName
- LastName
- DOB

vusrPersonNumbers contains:
- ID
- M_Num
- D_Num
- SiteID
- SiteCode

vusrPerson will only contain one row/instance for each person.

vusrPersonNumbers is the one that has multiple entries. The ID is unique but is repeated throughout the table/userview depending on whether the person has multiple values for those other fields mentioned (M_Num, D_Num, SiteID, SiteCode).

Attached is a sample file of both userviews/tables. So in this scenario, I will probably get back the following results (for no M#):

ID
1234
6979

ID 1234 should NOT be returned as a result because it does have an M# but just not on that specific row. Is it possible to have all those entries treated as "ONE" row in a sense?

The query I'm using is:

Select DISTINCT vusrPersonNumbers.ID, M_Num, D_Num
From   vusrPersonNumbers
       Inner Join (
         Select   ID
         From     vusrPersonNumbers
         Group By ID
         Having Count(NullIf(M_Num, '')) = 0
		 ) As No_M_Value
		 On vusrPersonNumbers.ID= No_M_Value.ID
ORDER BY vusrPersonNumbers.ID

I know it doesn't include vusrPerson and if you can let me know how to join that as well, that would be great.

Thanks.

 

[gmmastros]

Based on the sample data you provided, and the code you show, ID 1234 is not returned by the query. You are getting 2 rows in the output because the query is returning distinct id, m_num, and d_num. This id has 2 different values for d_num.

I took your sample data and use it to put data in to table variables. This allows me to test the code without actually having your tables available to me.

Declare @vusrPerson Table(Id Int, LastName VarChar(20), FirstName VarChar(20), DOB DateTime)
			
Insert Into @vusrPerson Values(1234,'Smith','John' ,'1/17/1968')
Insert Into @vusrPerson Values(4598,'Doe'  ,'Jane' ,'3/29/1975')
Insert Into @vusrPerson Values(6979,'Test' ,'Frank','9/15/1980')

Declare @vusrPersonNumbers Table(ID Int, M_Num VarChar(20), D_Num VarChar(20), SiteId Int)
			
Insert Into @vusrPersonNumbers Values(1234,	'09820098',	NULL,     1)
Insert Into @vusrPersonNumbers Values(1234,	'A98729077','D132987',2)
Insert Into @vusrPersonNumbers Values(1234,	'A98729078','D132988',4)
Insert Into @vusrPersonNumbers Values(1234,	'A98729079','D132989',6)
Insert Into @vusrPersonNumbers Values(1234,	NULL,       NULL,     7)
Insert Into @vusrPersonNumbers Values(4598,	'B89826789',NULL,     2)
Insert Into @vusrPersonNumbers Values(4598,	'B89826790',NULL,     5)
Insert Into @vusrPersonNumbers Values(4598,	'B89826791',NULL,     6)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D103676',16)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D103676',15)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D103676',14)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D000001',9)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D000001',8)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D000001',7)

Select DISTINCT vusrPersonNumbers.ID, M_Num, D_Num, vusrPerson.*
From   @vusrPersonNumbers As vusrPersonNumbers
       Inner Join (
         Select   ID
         From     @vusrPersonNumbers
         Group By ID
         Having Count(NullIf(M_Num, '')) = 0
		 ) As No_M_Value
		 On vusrPersonNumbers.ID= No_M_Value.ID
       Inner Join @vusrPerson As vusrPerson
		On vusrPersonNumbers.ID = vusrPerson.ID

ORDER BY vusrPersonNumbers.ID

In the code above, I join to the Person table so that it also returns the name and DOB. Please copy/paste this to a query window and run it. If this does not provide the results you want, please let me know (and describe what is wrong with it).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Getting the following error:

Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@vusrPersonNumbers".
Msg 1087, Level 15, State 2, Line 5
Must declare the table variable "@vusrPersonNumbers".

Tried to read up on how to declare this properly but couldn't figure it out.

 

[gmmastros]

Did you copy/paste everything in the code window?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Sorry. Just did that and got the results returned.

6979 was returned twice. All values are the same except for the D_Num column. This is the part that I couldn't figure out. I want this to return only once. Other than that, it's exactly what I'm looking for since it didn't return 1234 which should be excluded and is.

I think we're getting there

Let me know what to do next.

Thanks.

 

[gmmastros]

You say that you only want it to return once. I get that. What do you want it to show? Do you want it to show a d number at all, the first one, the last one??? Once you answer this question, the rest should be easy-ish.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

In the end, I want the following columns returned:

ID
LastName, FirstName
DOB
M_Num
D_Num

It can show the first D_Num.

 

[gmmastros]

try this:

Declare @vusrPerson Table(Id Int, LastName VarChar(20), FirstName VarChar(20), DOB DateTime)
			
Insert Into @vusrPerson Values(1234,'Smith','John' ,'1/17/1968')
Insert Into @vusrPerson Values(4598,'Doe'  ,'Jane' ,'3/29/1975')
Insert Into @vusrPerson Values(6979,'Test' ,'Frank','9/15/1980')

Declare @vusrPersonNumbers Table(ID Int, M_Num VarChar(20), D_Num VarChar(20), SiteId Int)
			
Insert Into @vusrPersonNumbers Values(1234,	'09820098',	NULL,     1)
Insert Into @vusrPersonNumbers Values(1234,	'A98729077','D132987',2)
Insert Into @vusrPersonNumbers Values(1234,	'A98729078','D132988',4)
Insert Into @vusrPersonNumbers Values(1234,	'A98729079','D132989',6)
Insert Into @vusrPersonNumbers Values(1234,	NULL,       NULL,     7)
Insert Into @vusrPersonNumbers Values(4598,	'B89826789',NULL,     2)
Insert Into @vusrPersonNumbers Values(4598,	'B89826790',NULL,     5)
Insert Into @vusrPersonNumbers Values(4598,	'B89826791',NULL,     6)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D103676',16)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D103676',15)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D103676',14)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D000001',9)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D000001',8)
Insert Into @vusrPersonNumbers Values(6979,	NULL,       'D000001',7)

Select vusrPersonNumbers.ID, 
       Min(M_Num) As M_Num, 
	   Min(D_Num) As D_Num, 
	   vusrPerson.DOB, 
	   vusrPerson.LastName, 
	   vusrPerson.FirstName
From   @vusrPersonNumbers As vusrPersonNumbers
       Inner Join (
         Select   ID
         From     @vusrPersonNumbers
         Group By ID
         Having Count(NullIf(M_Num, '')) = 0
		 ) As No_M_Value
		 On vusrPersonNumbers.ID= No_M_Value.ID
       Inner Join @vusrPerson As vusrPerson
		On vusrPersonNumbers.ID = vusrPerson.ID
Group By vusrPersonNumbers.ID,vusrPerson.DOB, vusrPerson.LastName, vusrPerson.FirstName
ORDER BY vusrPersonNumbers.ID

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Perfect. I was actually playing around using max earlier but couldn't get it working with the current code I was using previously. It would have worked either way (min or max) since I only want one unique ID outputted.

Let me know what the final code is and I'll do a final query to see what the output will look like.

Thanks George.

 

[gmmastros]

not much to clean up, but here it is.

Select vusrPersonNumbers.ID, 
       Min(M_Num) As M_Num, 
	   Min(D_Num) As D_Num, 
	   vusrPerson.DOB, 
	   vusrPerson.LastName, 
	   vusrPerson.FirstName
From   vusrPersonNumbers
       Inner Join (
         Select   ID
         From     vusrPersonNumbers
         Group By ID
         Having Count(NullIf(M_Num, '')) = 0
		 ) As No_M_Value
		 On vusrPersonNumbers.ID= No_M_Value.ID
       Inner Join vusrPerson
		On vusrPersonNumbers.ID = vusrPerson.ID
Group By vusrPersonNumbers.ID,vusrPerson.DOB, vusrPerson.LastName, vusrPerson.FirstName
ORDER BY vusrPersonNumbers.ID

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk17]

Thanks for all the help George. That did the job

 

[gmmastros]

You're welcome.

Do you fully understand everything I did. I think it's important that you understand these things because they are very power and useful techniques that you should understand to take your coding skills to the next level.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom