 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Programming / SQL / July 2008TSQL - self joined table
Hi all, I am working with the SSRS catalog database to create a report and got stuck on this query. The subquery is bringing back more than one row - so it errors out. Basically I want to return report names (Type=2) with the associated Parent Path. Here's my faulty query so far... SELECT *, (SELECT a.Path FROM Catalog a INNER JOIN Catalog b ON a.ParentID = b.ItemID) AS ParentPath FROM Catalog WHERE (Type = '2') Here's the partial DDL... CREATE TABLE [dbo].[Catalog] ( [ItemID] [uniqueidentifier] NOT NULL , [Path] [nvarchar] (425) NOT NULL , [Name] [nvarchar] (425) NOT NULL , [ParentID] [uniqueidentifier] NULL , [Type] [int] NOT NULL... Thanks in advance for your help. --Mike SELECT a.*, ParentPath = b.path FROM Catalog a INNER JOIN Catalog b ON b.ParentID = a.ItemID WHERE a.type = 2; -- INT, so no quotes! You had a subquery but it was not correlated to the outer query in any way. You only need to reference catalog twice, not thrice. :-) On 7/25/08 2:09 PM, in article #bQegGo7IHA.4112@TK2MSFTNGP05.phx.gbl, "Mike Foley" <mf@badazzmf.biz> wrote: > Hi all, > [quoted text clipped - 29 lines] > > Thanks in advance for your help. --Mike (Oh and you might want to make it a left outer join instead of an inner join, in case you want to include the top level.) On 7/25/08 2:09 PM, in article #bQegGo7IHA.4112@TK2MSFTNGP05.phx.gbl, "Mike Foley" <mf@badazzmf.biz> wrote: > Hi all, > [quoted text clipped - 29 lines] > > Thanks in advance for your help. --Mike Awesome.. thanks Aaron. One last question because I always marvel at good TSQL coders... Do you have any book or tutorial recommendations on learning TSQL? And I mean REALLY understanding it from the ground up. I'm tired of the haphazard sort of approach I've had over the years :-P --Mike > (Oh and you might want to make it a left outer join instead of an inner > join, in case you want to include the top level.) [quoted text clipped - 38 lines] >> >> Thanks in advance for your help. --Mike > Awesome.. thanks Aaron. > > One last question because I always marvel at good TSQL coders... Do you > have any book or tutorial recommendations on learning TSQL? And I mean > REALLY understanding it from the ground up. I'm tired of the haphazard > sort of approach I've had over the years :-P --Mike Most everything written about sql is from the waist up. You learn the code. If you'd like to get at the feet and hit the ground running, well that's another story. There's not alot written for below the belt. There's plenty of great books and sites that will belt you. I try to write about the sql underbelly, what is its foundation, what is the sql feet planted in. It depends if you want to read about feet and understand how the sql sausage is really made:) www.beyondsql.blogspot.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.