Query Help Part II 1

[overDeveloper]

I have the following query (thanks gmmastros)

Select A.Name + '/' + B.Name As OutputColumn
From   TableName A
       Inner Join TableName B
         On A.Id = B.ParentId
Union All

Select Name
From   TableName 
Where  ParentId = 0

Order By OutputColumn

Now, I need to do one change to it. I have a separate table that has a field that corresponds to a.id. I want to change it so that if Table C does not have an a.id value in that field then a.id is left out of the results. make Sense? I tried an inner join-ing Table c but that returned duplicate records....

 

[AlexCuse]

This perhaps? FYI - You probably get duplicates because you have duplicates in table C.

Select A.Name + '/' + B.Name As OutputColumn
From   TableName A
       Inner Join TableName B
         On A.Id = B.ParentId
Union All

Select Name
From   TableName 
Where  ParentId = 0
[b]and a.Id in (select ID from TableC)[/b]
Order By OutputColumn

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

I meant to put this in the part in bold

and a.Id in (select [b]distinct[/b] ID from TableC)

Ignorance of certain subjects is a great part of wisdom

 

[overDeveloper]

No, I get duplicates of everything (4 actually) even the ones that are not in table c at all... I'll try your code...

 

[overDeveloper]

nope - no luck - I still get the ones I want to be left out (the ones not in table c)

 

[AlexCuse]

Hm, that is strange. Oh well, try this out. I replaced table A with a subquery that should only return the records that you want. If this does not work please post the exact query you are using.

Select A.Name + '/' + B.Name As OutputColumn
From   
(
select * from TableName where 
ID in (select distinct ID 
from TableC
)) A
       Inner Join TableName B
         On A.Id = B.ParentId
Union All

Select Name
From   TableName 
Where  ParentId = 0
Order By OutputColumn

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

My suggestion is to take the original query and make it a subquery. Before doing so, you'll need to add the ID to the output of the subquery. Then, you inner join the subquery with your other table to filter the records you don't want. Like this...

Select A.OutputColumn
From   (
       Select A.Id, A.Name + '/' + B.Name As OutputColumn
       From   TableName A
              Inner Join TableName B
                On A.Id = B.ParentId

       Union All

       Select Id, Name
       From   TableName 
       Where  ParentId = 0
       ) As A
       Inner Join TableC
         On A.Id = TableC.Id
Order By OutputColumn

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[overDeveloper]

hmmm... neither of those do it... I either am still egtting all of them returned or all of them returned 4 fold... I am thinking it is proabably the first inner join that is dragging them all in ????

 

[gmmastros]

Sample data time!

If you show us some sample data with expected results, I'm sure we would be able to help you.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mharroff]

Select A.OutputColumn
From   (
       Select A.Id, A.Name + '/' + B.Name As OutputColumn
       From   TableName A
              Inner Join TableName B
                On A.Id = B.ParentId

       Union All

       Select Id, Name
       From   TableName 
       Where  ParentId = 0
       ) As A
[red]       Inner Join (Select Distinct ID FROM TableC) AS C
         On A.Id = C.Id[/red]
Order By OutputColumn

 

[overDeveloper]

ok - Table a/b looks like:

id name parentid
1 aaa 0
2 bbb 0
3 ccc 0
4 ZZZ 1
5 YYY 1
6 XXX 3

Table c looks like
id tableabid
1 3
2 3
3 5
4 5
5 2


so I would want the following returned

ccc
aaa/YYY
bbb

Make sense? mharroff, I tried your and am not getting the correct results....

 

[AlexCuse]

I am not so sure I follow your logic for this, but this query will return the desired result set. If I have managed to guess your business rules properly, then the key is to limit what you are joining using subqueries or derived tables.

Let me know if this works for you.

--SampleData, you don't need
declare @ab table (id int identity(1,1), name varchar(3), parentID int)
declare @c table (id int identity(1,1), tableabid int)

insert into @ab
select 'aaa', 0
union all select 'bbb', 0
union all select 'ccc', 0
union all select 'zzz', 1
union all select 'YYY', 1
union all select 'XXX', 3

insert into @c
select 3
union all select 3
union all select 5
union all select 5
union all select 2


--query (replace table variables names with real table names)
Select A.Name + isnull('/' + B.Name, '') As OutputColumn
From   (
	select * from @ab
	where parentID = 0
	) A
       Left Join 
	(
	select y.* from @ab y
	inner join @c z
	on y.ID = z.id
	)B
         On A.Id = B.ParentId
	 And B.ID in (select max(ID) from @ab group by ParentID)

Hope it helps, and post back with any specific questions.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[overDeveloper]

ok - if I add a distinct to the original select...

Select [b]distinct[/b] A.Name + isnull('/' + B.Name, '') As OutputColumn
From   (
    select * from @ab
    where parentID = 0
    ) A
       Left Join 
    (
    select y.* from @ab y
    inner join @c z
    on y.ID = z.id
    )B
         On A.Id = B.ParentId
     And B.ID in (select max(ID) from @ab group by ParentID)

..I get closer. The results I get now are correct EXCEPT that it leaves out the ones where parentID = 0 AND has records in table c so instead of getting

ccc
aaa/YYY
bbb

returned, I only get:

aaa/YYY

any ideas?

 

[AlexCuse]

If the query that I posted above does not work for you, then there is a problem with the sample data you provided.

(here is result set from the query, using sample data you provide to create table variables:

aaa/YYY
bbb
ccc

not ordered in the way you list, but close enough for government work. Also, I got the exact same result set when I added distinct)

Did you copy what I pasted in the code window EXACTLY when you tested this, and what was your result when you did?

Ignorance of certain subjects is a great part of wisdom

 

[overDeveloper]

I'm sure it is an issue with the data... it's weird - this:

And B.ID in (select max(ID) from @ab group by ParentID)

gives me the majority of the results I should have, and:

And B.ID in (select max(ID) from @ab group by ID)

seems to give me the ones the first on is missing....

 

[AlexCuse]

You realize that

select max(ID) from @ab group by ID

Will just return all ID's, correct? I thought from the fact taht you wanted aaa/YYY returned and not aaa/ZZZ that you only wanted to return the child record with the highest ID.

If you copy that whole block that I pasted in the code window into query analyzer (including establishing the sample data), do you get the desired result?

Ignorance of certain subjects is a great part of wisdom

 

[overDeveloper]

I am not sure what the problem is... I must be missing something - thanks for the help...

 

[Lamprey13]

Perhaos this will work for you?

SELECT DISTINCT
	CASE
		WHEN b.[Name] IS NULL THEN a.[Name]
		ELSE b.[Name] + COALESCE('/' + a.[Name], '')
	END AS OutputColumn
FROM
	(
		SELECT ab.id, ab.[name], ab.parentid
		FROM @ab ab
		INNER JOIN @c c
		ON ab.id = c.tableabid
	) a
LEFT OUTER JOIN
	@ab b
	ON b.id = a.parentid

 

[overDeveloper]

Beautious! That did it!