Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2007-10-22 : 11:17:06
|
| Trying to figure out recursive queries, but having a bit of trouble applying. I have a table with:wid - keyfield, not nullparentID - intstart_date - smalldatetimetitle -varChar(100)The structure is that if parentID=0 then it's the base of the lookup, meaning parent, but parentID could contain a wid number meaning that it's a subordinate item; e.g.1 0 5/2/2007 The parent2 1 5/3/2007 Child of the parent3 1 5/5/2007 Child 2 of the parent4 2 5/4/2007 Child of the child of parent5 2 5/5/2007 Child 2 of the child of the parent6 3 5/7/2007 Child of the child 2 of the parentTried the following, but instead of 27 results I get 300:use myDatabaseWith events (id, parent, start, title) as(select wid, parentID, start_date, title from workshop where WID=1566140 Union All select a.wid, a.parentID, a.start_date, a.title from workshop a inner joinworkshop b on b.parentID=a.wid)select * from events order by startAny help appreciate. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-22 : 11:23:21
|
Read about CTE (Common Table Expression) in Books Online, and check out the examples there.Try to adopt yo your environment and if you still have have problems, please post your try here.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2007-10-22 : 11:27:58
|
| Peso,I tried that first, reading two articles (one in bol, and one from the web) and am having trouble. I wouldn't have posted without trying it out first. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-22 : 11:34:36
|
[code]DECLARE @Sample TABLE (WID INT, ParentID INT, StartDate DATETIME, Title VARCHAR(100))INSERT @SampleSELECT 1, 0, '5/2/2007', 'The parent' UNION ALLSELECT 2, 1, '5/3/2007', 'Child of the parent' UNION ALLSELECT 3, 1, '5/5/2007', 'Child 2 of the parent' UNION ALLSELECT 4, 2, '5/4/2007', 'Child of the child of parent' UNION ALLSELECT 5, 2, '5/5/2007', 'Child 2 of the child of the parent' UNION ALLSELECT 6, 3, '5/7/2007', 'Child of the child 2 of the parent';WITH Yak (WID, ParentID, StartDate, Title)AS ( SELECT WID, ParentID, StartDate, Title FROM @Sample WHERE WID = 1 AND ParentID = 0 UNION ALL SELECT s.WID, s.ParentID, s.StartDate, s.Title FROM Yak AS y INNER JOIN @Sample AS s ON s.ParentID = y.WID)SELECT *FROM Yak[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-22 : 11:37:16
|
In your anchorpart, you select from the outer table which is correct.But in your recursive part, you are both selecting from outer table and self-joining the same outer table which is not correct.The recursive part must include same CTE and outer table, once.quote:
Originally posted by janetb
With events (id, parent, start, title) as(select wid, parentID, start_date, title from workshop where WID = 1566140Union All select a.wid, a.parentID, a.start_date, a.title from workshop a inner join workshop b on b.parentID = a.wid)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2007-10-22 : 13:50:57
|
| Thanks so much for the reply. Maybe I'm missing something, but it seems pretty much what I had? However, I took your code and substituted my table names, and again, I got about 300 rows.WITH Yak (WID, ParentID, StartDate, Title)AS ( SELECT WID, ParentID, Start_Date, Title FROM workshop WHERE WID = 1566140 AND ParentID = 0 UNION ALL SELECT s.WID, s.ParentID, s.Start_Date, s.Title FROM workshop AS y INNER JOIN workshop AS s ON s.ParentID = y.WID)SELECT * FROM YakResults:1566140 0 11/28/2007 Title The 1st An…1594643 1566140 11/28/2007 Title Day One - …1594644 1594643 11/28/2007 Title 1A - When …1594647 1594643 11/28/2007 Title 1B - Exact…1594648 1594643 11/28/2007 Title 1C - An Up…1594649 1594643 11/28/2007 Title 1D - Under…1594650 1594643 11/28/2007 Title 2E - When …1594651 1594643 11/28/2007 Title 2F - Exact…1594652 1594643 11/28/2007 Title 2G - An Up…1594653 1594643 11/28/2007 Title 2H - Under…1594654 1594643 11/28/2007 Title 3I - Navig…1594656 1594643 11/28/2007 Title 3J - Under…1594657 1594643 11/28/2007 Title 3K - Obtai…1594658 1594643 11/28/2007 Title 3L - In-Ho…1594659 1594643 11/28/2007 Title 4M - Worki…1594660 1594643 11/28/2007 Title 4N - Getti…1594661 1594643 11/28/2007 Title 4O - Getti…1594662 1594643 11/28/2007 Title 4P - Getti…1594663 1566140 11/29/2007 Title 11/29/07 -…1594664 1594663 11/29/2007 Title 5Q - Worki…1594665 1594663 11/29/2007 Title 5R - Navig…1594666 1594663 11/29/2007 Title 5S - This …1594667 1594663 11/29/2007 Title 5T - Obtai…1594668 1594663 11/29/2007 Title 6U - This …1594669 1594663 11/29/2007 Title 6V - Getti…1594670 1594663 11/29/2007 Title 6W - Getti…1594671 1594663 11/29/2007 Title 6X - Getti…108944 108943 6/29/2004 Title Somatic Ex…108945 108943 12/9/2004 Title Somatic Ex…119180 115640 8/6/2004 Title Internal F…119181 115640 10/15/2004 Title Internal F…190552 108940 10/29/2004 Title Internal F…192433 192432 10/29/2004 Title Clinical S…192434 192432 10/29/2004 Title Clinical S…192901 192432 10/29/2004 Title Clinical S…288049 288047 10/29/2004 Title (Session 2…309191 309190 10/29/2004 Title Navigating…311376 229168 10/29/2004 Title Mindfulnes…311378 229168 10/29/2004 Title Mindfulnes…311379 229168 10/29/2004 Title Mindfulnes…311380 229168 10/29/2004 Title Mindfulnes…311381 229168 11/2/2005 Title Mindfulnes…702889 640481 9/15/2005 Title Day One - …702890 702889 9/15/2005 Title A) Nutriti…702891 702889 9/15/2005 Title B) Dealing…702892 702889 9/15/2005 Title C) Speech …702893 702889 9/15/2005 Title D) Support…702894 702889 9/15/2005 Title E) Dealing…702895 702889 9/15/2005 Title F) Underst…702896 702889 9/15/2005 Title G) Treatme…702897 702889 9/15/2005 Title H) Support…702898 702889 9/15/2005 Title I) The Imp…702899 702889 9/15/2005 Title J) Underst…702900 702889 9/15/2005 Title K) Treatme…702901 702889 9/15/2005 Title L) Thinkin…702902 640481 9/16/2005 Title Day Two - …702903 702902 9/16/2005 Title M) Talking…702904 702902 9/16/2005 Title N) When It…702905 702902 9/16/2005 Title O) Speech …702906 702902 9/16/2005 Title P) Social …702907 702902 9/16/2005 Title Q) Talking…702908 702902 9/16/2005 Title R) Advocac…702909 702902 9/16/2005 Title S) Alterna…702910 702902 9/16/2005 Title T) Social …704250 639155 10/5/2005 Title A) All in …704251 639155 10/5/2005 Title B) Finding…704252 639155 10/5/2005 Title C) Long-Te…704253 639153 10/21/2005 Title A) Therape…704254 639153 10/21/2005 Title B) Do You …704255 639153 10/21/2005 Title C) Underst…704256 639153 10/21/2005 Title D) From Br…704257 628515 9/29/2005 Title A) Flops a…704259 628515 9/29/2005 Title B) Weaving…704260 628515 9/29/2005 Title C) Your Co…704261 628515 9/29/2005 Title D) Teachin…704262 628515 9/29/2005 Title E) Staying…704263 628515 9/29/2005 Title F) Magneti…895125 895124 3/28/2006 Title Day 1 - A.…895126 895124 3/28/2006 Title Day 1 - B…895127 895124 3/28/2006 Title Day 1 - C…895128 815920 3/28/2006 Title Day 2 - We…895129 895128 3/28/2006 Title Day 2 - D.…895130 895128 3/28/2006 Title Day 2 - E.…895131 895128 3/28/2006 Title Day 2 - F.…907941 815920 3/28/2006 Title Women’s He…907942 815920 3/28/2006 Title Day 1 - A…907943 815920 3/28/2006 Title Day 1 - B.…907944 815920 3/28/2006 Title Day 1 - C…912235 815921 3/28/2006 Title Day 2 - D.…912236 815921 3/28/2006 Title Day 2 - E.…912238 815921 3/28/2006 Title Day 2 - F.…922270 818718 4/5/2006 Title Thursday -…922271 818718 4/5/2006 Title Friday - M…962580 818703 4/5/2006 Title Day One - …962581 818703 4/5/2006 Title Day One W…962582 818703 4/5/2006 Title Day One We…962583 818703 4/5/2006 Title Day One We…962584 818703 4/5/2006 Title Day Two - …962585 818703 4/5/2006 Title Day One We…962586 818703 4/5/2006 Title Day One We…962587 818703 4/5/2006 Title Day One We…962588 818703 4/5/2006 Title Day Two Th…962589 818703 4/5/2006 Title Day Two Th…962590 818703 4/5/2006 Title Day Two Th…962591 818703 4/5/2006 Title Day Two Th…962592 818703 4/5/2006 Title Day Two Th…962593 818703 4/5/2006 Title Day Two Th…975636 815923 4/18/2006 Title 2:00 - A H…975637 815923 4/18/2006 Title 2:00 - B C…975646 815923 4/18/2006 Title 3:45 - A E…975647 815923 4/18/2006 Title 3:45 - B P…995942 814539 5/12/2006 Title Advanced 1…995943 814539 5/12/2006 Title Advanced 2…995944 814539 5/12/2006 Title Basic 1 - …995945 814539 5/12/2006 Title Basic 2 - …995946 814539 5/12/2006 Title Basic 3 - …995947 814539 5/12/2006 Title Basic 4 - …995948 814539 5/12/2006 Title Advanced 3…995949 814539 5/12/2006 Title Advanced 4…1016215 818718 5/5/2006 Title A. Stroke …1016217 818718 5/5/2006 Title B. Stroke …1016218 818718 5/5/2006 Title C. New Mo…1016219 818718 5/5/2006 Title D. Telestr…1016220 818718 5/5/2006 Title E. Hemorrh…1016221 818718 5/5/2006 Title F. Constra…1143216 1143215 9/29/2006 Title A.Ethical …1143217 1143215 9/29/2006 Title B.Ethical …1143218 1143215 9/29/2006 Title C.Ethical …1143219 1143215 9/29/2006 Title D.Ethical …1143220 1143215 9/29/2006 Title E.Boundari…1143221 1143215 9/29/2006 Title F.Conflict…1143222 1143215 9/29/2006 Title G. Provide…1143223 1143215 9/29/2006 Title H.Respecti…1166368 1166367 9/14/2006 Title Day One - …1166369 1166368 9/14/2006 Title 1A - The L…1166370 1166368 9/14/2006 Title 1B - Under…1166371 1166368 9/14/2006 Title 1C - Using…1166372 1166368 9/14/2006 Title 1D - Medic…1166373 1166368 9/14/2006 Title 2E - Trans…1166374 1166368 9/14/2006 Title 2F - Pragm…1166375 1166368 9/14/2006 Title 2G - Using…1166376 1166368 9/14/2006 Title 2H - Senso…1166377 1166368 9/14/2006 Title 3I - Dual …1166378 1166368 9/14/2006 Title 3J - Under…1166379 1166381 9/14/2006 Title 4L - Navig…1166380 1166368 9/14/2006 Title 3K - Senso…1166381 1166367 9/14/2006 Title Day Two - …1166383 1166381 9/14/2006 Title 4M - Under…1166384 1166381 9/14/2006 Title 4N - Worki…1166385 1166381 9/14/2006 Title 5O - Medic…1166386 1166381 9/14/2006 Title 5P - Knowi…1166387 1166381 9/14/2006 Title 5Q - Pleas…1166388 1166381 9/14/2006 Title 5R - Suppo…1166389 1166381 9/14/2006 Title 6T - Dual …1166390 1166381 9/14/2006 Title 6U - Pleas…1166391 1166381 9/14/2006 Title 6V - Suppo…1208681 1171061 10/13/2006 Title A - Drug D…1208682 1171061 10/13/2006 Title B - Public…1208683 1171061 10/13/2006 Title C - Famili…1208684 1171061 10/13/2006 Title D - Life E…1208685 1171061 10/13/2006 Title E - Develo…1208686 1171061 10/13/2006 Title F - Bariat…1208687 1171061 10/13/2006 Title G - Strate…1208688 1171061 10/13/2006 Title H - Accomm…1208689 1171061 10/13/2006 Title I - Screen…1208690 1171061 10/13/2006 Title J - Adoles…1208691 1171061 10/13/2006 Title K - Pitfal…1208692 1171061 10/13/2006 Title L - The Bu…1214981 1154744 10/4/2006 Title A. Restora…1214982 1154744 10/4/2006 Title B. Dementi…1214983 1154744 10/4/2006 Title C. Palliat…1214984 1154744 10/4/2006 Title D. Restora…1214985 1154744 10/4/2006 Title E. Dementi…1214986 1154744 10/4/2006 Title F. Palliat…1225209 1166381 9/15/2006 Title 6S - Knowi…1243431 1171063 11/3/2006 Title A. #34;Un…1243432 1171063 11/3/2006 Title B. “Findin…1243433 1171063 11/3/2006 Title C. “When F…1243434 1171063 11/3/2006 Title D. “I’m Li…1291430 1161687 12/1/2006 Title A. Are You…1291434 1161687 12/1/2006 Title B. Lower E…1360106 1312601 3/15/2007 Title A. Adding …1360107 1312601 3/15/2007 Title B. Barrier…1360108 1312601 3/15/2007 Title C. Coding …1360109 1312601 3/15/2007 Title D. Treatin…1360110 1312601 3/15/2007 Title A. Adding …1360111 1312601 3/15/2007 Title B. Barrier…1360112 1312601 3/15/2007 Title C. Coding …1360113 1312601 3/15/2007 Title D. Treatin…1365960 1307667 4/17/2007 Title A. Acute M…1365961 1307667 4/17/2007 Title B. An Inno…1365962 1307667 4/17/2007 Title C. Acciden…1365963 1307667 4/17/2007 Title D. The Day…1365964 1307667 4/17/2007 Title E. An Eval…1366827 1324842 4/20/207 Title 1A - Paren…1366828 1324842 4/20/207 Title 1B - Behav…1366829 1324842 4/20/207 Title 1C - The E…1366830 1324842 4/20/207 Title 2A - Paren…1366831 1324842 4/20/207 Title 2B - Behav…1366832 1324842 4/20/207 Title 2C - The E…1366833 1324842 4/20/207 Title 3A - Incor…1366834 1324842 4/20/207 Title 3B - Knowi…1366835 1324842 4/20/207 Title 3C - Creat…1394434 1308484 5/4/2007 Title B1. Tips…1394436 1308484 5/4/2007 Title A1. Heart …1394443 1308484 5/4/2007 Title A2.Type 1 …1394444 1308484 5/4/2007 Title B2. Diabet…1394445 1308484 5/4/2007 Title B3. Living…1394446 1308484 5/4/2007 Title A3. The Pu…1394447 1308484 5/4/2007 Title B4. Type 2…1394448 1308484 5/4/2007 Title A4. Diabet…1402230 1308494 4/5/2007 Title Day 1 - W…1402231 1402230 4/25/2007 Title Day 1 - A.…1402232 1402230 4/25/2007 Title Day 1 - B.…1402233 1402230 4/25/2007 Title Day 1 - C.…1402234 1402230 4/25/2007 Title Day 1 - D.…1402235 1402237 4/25/2007 Title Day 2 - G.…1402236 1402237 4/25/2007 Title Day 2 - F.…1402237 1308494 4/25/2007 Title Day 2 - Th…1402238 1402230 4/25/2007 Title Day 1 - E.…1402240 1402237 4/25/2007 Title Day 2 - H.…1402241 1402237 4/25/2007 Title Day 2 - I.…1402242 1402237 4/25/2007 Title Day 2 - J.…1421358 1403111 7/19/2007 Title Day 1 - La…1421359 1421358 7/19/2007 Title 1 - Models…1421360 1421358 7/19/2007 Title 2 - Latina…1421361 1421358 7/19/2007 Title 3 - Reachi…1421362 1403111 7/19/2007 Title Day 2 - L…1421363 1421362 7/19/2007 Title 4 - Effe…1421364 1421362 7/19/2007 Title 5 - Mental…1421365 1421362 7/19/2007 Title 6 - Keys t…1430063 1430062 6/15/2007 Title 1A - Integ…1430064 1430062 6/15/2007 Title 1B - End-o…1430065 1430062 6/15/2007 Title 1C - Home …1430066 1430062 6/15/2007 Title 1D - Drivi…1430067 1430062 6/15/2007 Title 2E - New G…1430068 1430062 6/15/2007 Title 2F - Overv…1430069 1430062 6/15/2007 Title 2G - Delir…1430940 1430062 6/15/2007 Title 2H - Impro…1430941 1430062 6/15/2007 Title 3I - Pract…1430942 1430062 6/15/2007 Title 3J - Diagn…1430943 1430062 6/15/2007 Title 3K - Subst…1430944 1430062 6/15/2007 Title 3L - Medic…1511244 1511243 10/3/2007 Title A. #34;It…1511246 1511243 10/3/2007 Title B. “Givin’…1511248 1511243 10/3/2007 Title C. “Mercy …1511249 1511243 10/3/2007 Title D. “It’s Y…1511250 1511243 10/3/2007 Title E. “Givin’…1511251 1511243 10/3/2007 Title F. “Mercy …1520390 1501222 9/13/2007 Title Day One - …1520391 1520390 9/13/2007 Title 1A - Navig…1520392 1520390 9/13/2007 Title 1B - Under…1520393 1520390 9/13/2007 Title 1C - Anxie…1520394 1520390 9/13/2007 Title 1D - Disru…1520395 1520390 9/13/2007 Title 2E - Navig…1520396 1520390 9/13/2007 Title 2F - Sexua…1520397 1520390 9/13/2007 Title 2G - Mood …1520398 1520390 9/13/2007 Title 2H - Anxie…1520399 1520390 9/13/2007 Title 3I - Under…1520400 1520390 9/13/2007 Title 3J - Disru…1520401 1520390 9/13/2007 Title 3K - Mood …1520402 1520403 9/13/2007 Title 4L - Sexua…1520403 1501222 9/13/2007 Title Day Two - …1520404 1520403 9/13/2007 Title 4M - Mood …1520405 1520403 9/13/2007 Title 4N - Anxie…1520406 1520403 9/13/2007 Title 4O - Under…1520409 1520403 9/13/2007 Title 5P - Disru…1520410 1520403 9/13/2007 Title 5Q - Mood …1520411 1520403 9/13/2007 Title 5R - Under…1520412 1520403 9/13/2007 Title 6T - Your …1520413 1520403 9/13/2007 Title 6U - Anxie…1520414 1520403 9/13/2007 Title 6V - Disru…1521325 1513107 9/27/2007 Title A - Death …1521326 1513107 9/27/2007 Title B - Moral …1521327 1513107 9/27/2007 Title C - Learni…1521328 1513107 9/27/2007 Title D - Corpor…1521329 1513107 9/27/2007 Title E - Death …1521330 1513107 9/27/2007 Title F - Moral …1521333 1513107 9/27/2007 Title G - Learni…1521334 1513107 9/27/2007 Title H - Corpor…1530431 1520403 9/14/2007 Title 6S - Livin…1545014 1512171 10/11/2007 Title A. Stabili…1545015 1512171 10/11/2007 Title B. Sleep A…1545016 1512171 10/11/2007 Title C. Treatme…1545017 1512171 10/11/2007 Title D. Body P…1545018 1512171 10/11/2007 Title E. Patient…1557852 1512185 11/12/2007 Title Touching I…1557853 1512185 11/12/2007 Title Period of …1564287 1563364 10/21/2007 Title A - Diabet…1564288 1563364 10/21/2007 Title B - Asthma…1564289 1563364 10/21/2007 Title C - Hypert…1564290 1563364 10/21/2007 Title D - Manage…1564291 1563364 10/21/2007 Title E - Insuli…1564292 1563364 10/21/2007 Title F - Asthma…1564293 1563364 10/21/2007 Title G - Renin …1564294 1563364 10/21/2007 Title H - Intens…1564295 1563364 10/21/2007 Title I - Carboh…1564296 1563364 10/21/2007 Title J - Smokin…1564297 1563364 10/21/2007 Title K - Treati…1564298 1563364 10/21/2007 Title L - Drug I… |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2007-10-22 : 14:01:37
|
| Thanks for your time. Probably my misunderstanding, but got this to work - linking it to the outer most query (yak) rather than the subquery after the union:with yak (wid, pid, start_date, title)as( -- the anchor member select wid, parentID, start_date, title from ceClasses.dbo.workshop where wid = 1566140 -- the recursive member union all select w.wid, w.parentid, w.start_date, w.title from ceClasses.dbo.workshop w -- the key is to join to the CTE join yak e on e.wid = w.parentid)select * from yak |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-23 : 01:42:45
|
Great you figured it out!
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
sql2005 recursive query