sql query

[wbodger]

Ok ,I am creating a view and one little piece is causing me fits, not sure why I can't come up with this. Here is the situation: I have a table with a specid, pptid and pspecid. Now, specid is always there, but if it was not the original specimen, then pptid may be blank, in which case I want to use the pptid from the pspecid, so the data

specid pptid  pspecid
123456 654321 blank
234567 blank  654321
345678 blank  654321
456789 blank  654321

so, in my query I would like all of the above records to come up with pptid=654321 and I am drawing a complete blank. Any help?

wb

 

[markros]

select SpecID, pptID as OriginalPptID, 
coalesce(nullif(pptID,''), pspecID) as NewpptID
from DataTable

PluralSight Learning Library

 

[wbodger]

Actually, I tried to write this out too quickly so I could make it to my carpool last night. If the data looks like

specid pptid  pspecid
123456 654321 blank
234567 blank  123456
345678 blank  123456
456789 blank  123456
113355 44332  blank
224466 blank  113355
335577 26655  blank
446688 blank  335577

I need the query to return this

specid pptid  pspecid
123456 654321 blank
234567 654321 123456
345678 654321 123456
456789 654321 123456
113355 44332  blank
224466 44332  113355
335577 26655  blank
446688 26655  335577

So, if pptid is blank, then I want to pull the pptid of the pspecid (parentspecimenid). Thanks.

wb

 

[gmmastros]

This is not a terribly difficult query to write, but it does require a trick or two.

To write this query, you will need to join your table back to itself. This is considered a "Self Join". When you write a query like this, it is required to use table aliases.

Of course, some of the data will be NULL that you are joining on, so you will want to write this as a left join so that you will get all of your data.

Something like this:

Select  A.SpecId,
        Coalesce(A.pptid, B.pptid) as pptid,
        Coalesce(A.pspecid, B.pspecid) As pspecid
From    [!]YourTableNameHere[/!] As A
        Left Join [!]YourTableNameHere[/!] As B
          On A.pspecid = B.specid

Notice the table aliases I used here (A and B). When you use table aliases, you must use them everywhere in the query. This includes the on clause, the select clause, and the where clause (not shown/needed here).

Also notice the Coalesce function. Some of the rows have NULL for the pptid column. When we join back to the parent row, we will get a valid pptid value. The coalesce function will return the first value in the paramter list that is not null. In this case, rows that have a pptid value will be returned. Rows that do not have a pptid value will use the parent row's pptid value.

Make sense?

-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

 

[wbodger]

What I have tried is a CASE statement

case 
	when PptID='' then (select a.pptid from tblspecimenbank195 as a, tblspecimenbank195 as b where a.parentspecimenid=b.specimenid)
	else PptID 
END

which does not quite work...

 

[gmmastros]

Even if it had worked, it would have been slower than the query I suggested.

-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

 

[wbodger]

Ha, that did it, thanks, George! I need to learn coalesce a bit better, I guess.

 

[wbodger]

Hmm... Looks like either 1) I spoke too soon or 2) I am not quite getting how to properly implement this. Perhaps a more complete explanation would be in order. I am writing a query to create a view into several tables that hold specimen data. This early step is needed to determine the actual participantid (pptid) for each specimenid. This piece is all in one table, by the way. What happens is the initial specimen may be separated into several specimens where only the initial specimen has a participant id directly associated with it. Then the 'child' specimens have a pointer to the parentspecimenid. So, what I need to do is grab the participant id from the specimenid that corresponds to the parentspecimenid. In this table, specimenid and parentspecimenid will never both be populated or both be blank (not null, just blank). I have walked thru parent/child hierarchies before, so I am not sure why this one is giving me such problems, but I cannot seem to wrap my finger around it. Then, once I have found the proper participantid, then I need to use it in a calculation a little further down in the view creation...

wb

 

[markros]

To me it looks like you simply need to self-join (assuming always just 1 level of hierarchy).

PluralSight Learning Library