|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL questionI have no idea what to do with this. I appreciate your consideration. CREATE TABLE [dbo].[DisplayValues] ( [u_key] [int], [GroupNumber] [int] NULL , [u_desc] [nvarchar] (50) , [u_parentkey] [int] NULL , [u_childkey] [int] NULL ) ON [PRIMARY] GO insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) insert into DisplayValues values (2, 1, 'Assault', 98, 0) insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, 0) insert into DisplayValues values (4, 1, 'Contraband', 97,0) insert into DisplayValues values (5, 1, 'Fall', 96, 0) insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) insert into DisplayValues values (14, 20, 'Patient to Visitor',0 , 98) CREATE TABLE [dbo].[DataValues] ( [ID_] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [u_Key] [int] NULL , ) ON [PRIMARY] GO insert into DataValues values (1) insert into DataValues values (2) insert into DataValues values (3) insert into DataValues values (12) insert into DataValues values (14) Basically, I want to grab the parent 'u_desc' value where the u_key record has a u_childkey value relates to the u_parentkey. If the u_key value points to a record that has no u_childkey defined, then I would just grab the u_desc value. For example, my result set would look like u_key u_desc 1 Accident/Injury 2 Assault 12 Assault 13 Assault 3 Alleged Staff-to-Pt Abuse In this example, if the u_key value is 12, I don't want the description of 'Patient to Staff' to be displayed. I would like 'Assault' to be displayed because the u_parentkey=u_childkey. I think the zeros (or nulls) in the u_parentkey and u_childkey have to be ignored because those are not really to be used in the join. Thank you for all your help. -Jack Jack,
First, let me express my appreciation for the DDL and data INSERTs. Thanks! I'm confused on this. Your results has u_key 13, yet I don't follow how it should be included. (I would have though that u_key=14 should have been in the results instead. Are you open to suggestions for Table re-organization? This design seems extremely unwieldly for the desired results. -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Jack" <j***@jack.net> wrote in message news:Sebpg.115424$Ce1.5381@dukeread01...Show quote > Hello, > I have no idea what to do with this. I appreciate your consideration. > > > CREATE TABLE [dbo].[DisplayValues] ( > [u_key] [int], > [GroupNumber] [int] NULL , > [u_desc] [nvarchar] (50) , > [u_parentkey] [int] NULL , > [u_childkey] [int] NULL > ) ON [PRIMARY] > GO > > insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) > insert into DisplayValues values (2, 1, 'Assault', 98, 0) > insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, > 0) > insert into DisplayValues values (4, 1, 'Contraband', 97,0) > insert into DisplayValues values (5, 1, 'Fall', 96, 0) > insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) > insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) > insert into DisplayValues values (14, 20, 'Patient to Visitor',0 , 98) > > > CREATE TABLE [dbo].[DataValues] ( > [ID_] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , > [u_Key] [int] NULL , > ) ON [PRIMARY] > GO > > insert into DataValues values (1) > insert into DataValues values (2) > insert into DataValues values (3) > insert into DataValues values (12) > insert into DataValues values (14) > > Basically, I want to grab the parent 'u_desc' value where the u_key record > has a u_childkey value relates to the u_parentkey. If the u_key value > points to a record that has no u_childkey defined, then I would just grab > the u_desc value. > > For example, my result set would look like > u_key u_desc > 1 Accident/Injury > 2 Assault > 12 Assault > 13 Assault > 3 Alleged Staff-to-Pt Abuse > > In this example, if the u_key value is 12, I don't want the description of > 'Patient to Staff' to be displayed. I would like 'Assault' to be > displayed because the u_parentkey=u_childkey. I think the zeros (or > nulls) in the u_parentkey and u_childkey have to be ignored because those > are not really to be used in the join. > > Thank you for all your help. > -Jack > Arnie,
You are correct. I made a mistake with the result set. It should be a '14' instead of a '13'. This structure is unwieldy, but we cannot change it unfortunately. This is used to do some dynamic page builds on a web page. The u_parentkey is a main selection, and the relation to the u_childkey are the options for the main group. I would really like to get the main group heading in a column, then the sub-group description as another column. Perhaps a better result set would be 1 Accident/Injury <blank> 2 Assault <blank> 3 Alleged Staff-to-Pt Abuse <blank> 12 Assault 'Patient to Staff' 13 Assault 'Patient to Patient' insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) insert into DisplayValues values (2, 1, 'Assault', 98, 0) insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, 0) insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) Show quote "Arnie Rowland" <ar***@1568.com> wrote in message news:OpF8HCGnGHA.4788@TK2MSFTNGP02.phx.gbl... > Jack, > > First, let me express my appreciation for the DDL and data INSERTs. > Thanks! > > I'm confused on this. Your results has u_key 13, yet I don't follow how it > should be included. (I would have though that u_key=14 should have been in > the results instead. > > Are you open to suggestions for Table re-organization? This design seems > extremely unwieldly for the desired results. > > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "Jack" <j***@jack.net> wrote in message > news:Sebpg.115424$Ce1.5381@dukeread01... >> Hello, >> I have no idea what to do with this. I appreciate your consideration. >> >> >> CREATE TABLE [dbo].[DisplayValues] ( >> [u_key] [int], >> [GroupNumber] [int] NULL , >> [u_desc] [nvarchar] (50) , >> [u_parentkey] [int] NULL , >> [u_childkey] [int] NULL >> ) ON [PRIMARY] >> GO >> >> insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) >> insert into DisplayValues values (2, 1, 'Assault', 98, 0) >> insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, >> 0) >> insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) >> insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) >> >> >> CREATE TABLE [dbo].[DataValues] ( >> [ID_] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , >> [u_Key] [int] NULL , >> ) ON [PRIMARY] >> GO >> >> insert into DataValues values (1) >> insert into DataValues values (2) >> insert into DataValues values (3) >> insert into DataValues values (12) >> insert into DataValues values (14) >> >> Basically, I want to grab the parent 'u_desc' value where the u_key >> record has a u_childkey value relates to the u_parentkey. If the u_key >> value points to a record that has no u_childkey defined, then I would >> just grab the u_desc value. >> >> For example, my result set would look like >> u_key u_desc >> 1 Accident/Injury >> 2 Assault >> 12 Assault >> 13 Assault >> 3 Alleged Staff-to-Pt Abuse >> >> In this example, if the u_key value is 12, I don't want the description >> of 'Patient to Staff' to be displayed. I would like 'Assault' to be >> displayed because the u_parentkey=u_childkey. I think the zeros (or >> nulls) in the u_parentkey and u_childkey have to be ignored because those >> are not really to be used in the join. >> >> Thank you for all your help. >> -Jack >> > > Select d1.u_key, d1.u_desc, Coalesce(d2.u_desc, '')
From DisplayValues d1 Left Outer Join DisplayValues d2 On d1.u_childkey = d2.u_parentkey And d1.u_childkey <> 0 Order by d1.u_key Tom "Jack" <j***@jack.net> wrote in message news:ijcpg.115444$Ce1.112558@dukeread01...Show quote > Arnie, > You are correct. I made a mistake with the result set. It should be a > '14' instead of a '13'. This structure is unwieldy, but we cannot change > it unfortunately. > > This is used to do some dynamic page builds on a web page. The > u_parentkey is a main selection, and the relation to the u_childkey are > the options for the main group. > > I would really like to get the main group heading in a column, then the > sub-group description as another column. > > Perhaps a better result set would be > 1 Accident/Injury <blank> > 2 Assault <blank> > 3 Alleged Staff-to-Pt Abuse <blank> > 12 Assault 'Patient to Staff' > 13 Assault 'Patient to Patient' > > insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) > insert into DisplayValues values (2, 1, 'Assault', 98, 0) > insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, > 0) > insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) > insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:OpF8HCGnGHA.4788@TK2MSFTNGP02.phx.gbl... >> Jack, >> >> First, let me express my appreciation for the DDL and data INSERTs. >> Thanks! >> >> I'm confused on this. Your results has u_key 13, yet I don't follow how >> it should be included. (I would have though that u_key=14 should have >> been in the results instead. >> >> Are you open to suggestions for Table re-organization? This design seems >> extremely unwieldly for the desired results. >> >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "Jack" <j***@jack.net> wrote in message >> news:Sebpg.115424$Ce1.5381@dukeread01... >>> Hello, >>> I have no idea what to do with this. I appreciate your consideration. >>> >>> >>> CREATE TABLE [dbo].[DisplayValues] ( >>> [u_key] [int], >>> [GroupNumber] [int] NULL , >>> [u_desc] [nvarchar] (50) , >>> [u_parentkey] [int] NULL , >>> [u_childkey] [int] NULL >>> ) ON [PRIMARY] >>> GO >>> >>> insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) >>> insert into DisplayValues values (2, 1, 'Assault', 98, 0) >>> insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, >>> 0) >>> insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) >>> insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) >>> >>> >>> CREATE TABLE [dbo].[DataValues] ( >>> [ID_] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , >>> [u_Key] [int] NULL , >>> ) ON [PRIMARY] >>> GO >>> >>> insert into DataValues values (1) >>> insert into DataValues values (2) >>> insert into DataValues values (3) >>> insert into DataValues values (12) >>> insert into DataValues values (14) >>> >>> Basically, I want to grab the parent 'u_desc' value where the u_key >>> record has a u_childkey value relates to the u_parentkey. If the u_key >>> value points to a record that has no u_childkey defined, then I would >>> just grab the u_desc value. >>> >>> For example, my result set would look like >>> u_key u_desc >>> 1 Accident/Injury >>> 2 Assault >>> 12 Assault >>> 13 Assault >>> 3 Alleged Staff-to-Pt Abuse >>> >>> In this example, if the u_key value is 12, I don't want the description >>> of 'Patient to Staff' to be displayed. I would like 'Assault' to be >>> displayed because the u_parentkey=u_childkey. I think the zeros (or >>> nulls) in the u_parentkey and u_childkey have to be ignored because >>> those are not really to be used in the join. >>> >>> Thank you for all your help. >>> -Jack >>> >> >> > > Thanks Jack,
Based on the data and desired output, this should get you the desired output. SELECT d1.u_Key , CASE WHEN len(d1.u_Desc) > 0 AND len(d2.u_Desc) > 0 THEN d2.u_Desc --ELSE d1.u_Desc ELSE d1.u_Desc END AS 'u_Desc' FROM DataValues dv JOIN DisplayValues d1 ON d1.u_Key = dv.u_Key LEFT JOIN DisplayValues d2 ON ( d1.u_ChildKey = d2.u_ParentKey AND d1.u_ChildKey <> 0 ) ORDER BY d1.u_Key -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Jack" <j***@jack.net> wrote in message news:ijcpg.115444$Ce1.112558@dukeread01... > Arnie, > You are correct. I made a mistake with the result set. It should be a '14' > instead of a '13'. This structure is unwieldy, but we cannot change it > unfortunately. > > This is used to do some dynamic page builds on a web page. The u_parentkey > is a main selection, and the relation to the u_childkey are the options for > the main group. > > I would really like to get the main group heading in a column, then the > sub-group description as another column. > > Perhaps a better result set would be > 1 Accident/Injury <blank> > 2 Assault <blank> > 3 Alleged Staff-to-Pt Abuse <blank> > 12 Assault 'Patient to Staff' > 13 Assault 'Patient to Patient' > > insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) > insert into DisplayValues values (2, 1, 'Assault', 98, 0) > insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, 0) > insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) > insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:OpF8HCGnGHA.4788@TK2MSFTNGP02.phx.gbl... >> Jack, >> >> First, let me express my appreciation for the DDL and data INSERTs. >> Thanks! >> >> I'm confused on this. Your results has u_key 13, yet I don't follow how it >> should be included. (I would have though that u_key=14 should have been in >> the results instead. >> >> Are you open to suggestions for Table re-organization? This design seems >> extremely unwieldly for the desired results. >> >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "Jack" <j***@jack.net> wrote in message >> news:Sebpg.115424$Ce1.5381@dukeread01... >>> Hello, >>> I have no idea what to do with this. I appreciate your consideration. >>> >>> >>> CREATE TABLE [dbo].[DisplayValues] ( >>> [u_key] [int], >>> [GroupNumber] [int] NULL , >>> [u_desc] [nvarchar] (50) , >>> [u_parentkey] [int] NULL , >>> [u_childkey] [int] NULL >>> ) ON [PRIMARY] >>> GO >>> >>> insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) >>> insert into DisplayValues values (2, 1, 'Assault', 98, 0) >>> insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, >>> 0) >>> insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) >>> insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) >>> >>> >>> CREATE TABLE [dbo].[DataValues] ( >>> [ID_] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , >>> [u_Key] [int] NULL , >>> ) ON [PRIMARY] >>> GO >>> >>> insert into DataValues values (1) >>> insert into DataValues values (2) >>> insert into DataValues values (3) >>> insert into DataValues values (12) >>> insert into DataValues values (14) >>> >>> Basically, I want to grab the parent 'u_desc' value where the u_key >>> record has a u_childkey value relates to the u_parentkey. If the u_key >>> value points to a record that has no u_childkey defined, then I would >>> just grab the u_desc value. >>> >>> For example, my result set would look like >>> u_key u_desc >>> 1 Accident/Injury >>> 2 Assault >>> 12 Assault >>> 13 Assault >>> 3 Alleged Staff-to-Pt Abuse >>> >>> In this example, if the u_key value is 12, I don't want the description >>> of 'Patient to Staff' to be displayed. I would like 'Assault' to be >>> displayed because the u_parentkey=u_childkey. I think the zeros (or >>> nulls) in the u_parentkey and u_childkey have to be ignored because those >>> are not really to be used in the join. >>> >>> Thank you for all your help. >>> -Jack >>> >> >> > > This even gets your second 'desired' output. Change the derived column names (Primary/Secondary) as you need.
SELECT d1.u_Key , CASE WHEN len(d1.u_Desc) > 0 AND len(d2.u_Desc) > 0 THEN d2.u_Desc ELSE d1.u_Desc END AS 'Primary' , CASE WHEN len(d1.u_Desc) > 0 AND len(d2.u_Desc) > 0 THEN d1.u_Desc ELSE '' END as 'Secondary' FROM DataValues dv JOIN DisplayValues d1 ON d1.u_Key = dv.u_Key LEFT JOIN DisplayValues d2 ON ( d1.u_ChildKey = d2.u_ParentKey AND d1.u_ChildKey <> 0 ) ORDER BY d1.u_Key -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Arnie Rowland" <ar***@1568.com> wrote in message news:%23rBf0jInGHA.4240@TK2MSFTNGP02.phx.gbl... Thanks Jack,Based on the data and desired output, this should get you the desired output. SELECT d1.u_Key , CASE WHEN len(d1.u_Desc) > 0 AND len(d2.u_Desc) > 0 THEN d2.u_Desc --ELSE d1.u_Desc ELSE d1.u_Desc END AS 'u_Desc' FROM DataValues dv JOIN DisplayValues d1 ON d1.u_Key = dv.u_Key LEFT JOIN DisplayValues d2 ON ( d1.u_ChildKey = d2.u_ParentKey AND d1.u_ChildKey <> 0 ) ORDER BY d1.u_Key -- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam Show quote "Jack" <j***@jack.net> wrote in message news:ijcpg.115444$Ce1.112558@dukeread01... > Arnie, > You are correct. I made a mistake with the result set. It should be a '14' > instead of a '13'. This structure is unwieldy, but we cannot change it > unfortunately. > > This is used to do some dynamic page builds on a web page. The u_parentkey > is a main selection, and the relation to the u_childkey are the options for > the main group. > > I would really like to get the main group heading in a column, then the > sub-group description as another column. > > Perhaps a better result set would be > 1 Accident/Injury <blank> > 2 Assault <blank> > 3 Alleged Staff-to-Pt Abuse <blank> > 12 Assault 'Patient to Staff' > 13 Assault 'Patient to Patient' > > insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) > insert into DisplayValues values (2, 1, 'Assault', 98, 0) > insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, 0) > insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) > insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:OpF8HCGnGHA.4788@TK2MSFTNGP02.phx.gbl... >> Jack, >> >> First, let me express my appreciation for the DDL and data INSERTs. >> Thanks! >> >> I'm confused on this. Your results has u_key 13, yet I don't follow how it >> should be included. (I would have though that u_key=14 should have been in >> the results instead. >> >> Are you open to suggestions for Table re-organization? This design seems >> extremely unwieldly for the desired results. >> >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "Jack" <j***@jack.net> wrote in message >> news:Sebpg.115424$Ce1.5381@dukeread01... >>> Hello, >>> I have no idea what to do with this. I appreciate your consideration. >>> >>> >>> CREATE TABLE [dbo].[DisplayValues] ( >>> [u_key] [int], >>> [GroupNumber] [int] NULL , >>> [u_desc] [nvarchar] (50) , >>> [u_parentkey] [int] NULL , >>> [u_childkey] [int] NULL >>> ) ON [PRIMARY] >>> GO >>> >>> insert into DisplayValues values (1, 1, 'Accident/Injury',0,0) >>> insert into DisplayValues values (2, 1, 'Assault', 98, 0) >>> insert into DisplayValues values (3, 1, 'Alleged Staff-to-Pt Abuse', 99, >>> 0) >>> insert into DisplayValues values (12, 20, 'Patient to Staff',0 , 98) >>> insert into DisplayValues values (13, 20, 'Patient to Patient',0 , 98) >>> >>> >>> CREATE TABLE [dbo].[DataValues] ( >>> [ID_] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , >>> [u_Key] [int] NULL , >>> ) ON [PRIMARY] >>> GO >>> >>> insert into DataValues values (1) >>> insert into DataValues values (2) >>> insert into DataValues values (3) >>> insert into DataValues values (12) >>> insert into DataValues values (14) >>> >>> Basically, I want to grab the parent 'u_desc' value where the u_key >>> record has a u_childkey value relates to the u_parentkey. If the u_key >>> value points to a record that has no u_childkey defined, then I would >>> just grab the u_desc value. >>> >>> For example, my result set would look like >>> u_key u_desc >>> 1 Accident/Injury >>> 2 Assault >>> 12 Assault >>> 13 Assault >>> 3 Alleged Staff-to-Pt Abuse >>> >>> In this example, if the u_key value is 12, I don't want the description >>> of 'Patient to Staff' to be displayed. I would like 'Assault' to be >>> displayed because the u_parentkey=u_childkey. I think the zeros (or >>> nulls) in the u_parentkey and u_childkey have to be ignored because those >>> are not really to be used in the join. >>> >>> Thank you for all your help. >>> -Jack >>> >> >> > > >> I have no idea what to do with this. I appreciate your consideration.<< You can write some ghod-awful convoluted queries to go thru all thepaths in your adjacency list model of a hierarchy, but wouldn't it be much easier to build a hierarchical code sysem (a la Dewey Decimal) ? CREATE TABLE IncidentCodes (incident_code CHAR(3) DEFAULT '000' NOT NULL CHECK (incident_code LIKE '[0-9][0-9][0-9]', incident_descr VARCHAR(35) NOT NULL); The query is insanely easy, the codes are easy to use and you can expand the codes in the future if you need more details. For example: (500, 'Assaults') (510, 'Assault: Patient to Staff') (520, 'Assault: Patient to Patient') (530, 'Assault: Patient to Visitor') (531, 'Assault: Patient to Visitor: no arrest') (532, 'Assault: Patient to Visitor: arrest') SQL is both DDL and DML. Quereis are not the only way to solve a problem.  |
|||||||||||||||||||||||
Other interesting topics