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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 merge two results in one table

Author  Topic 

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-11 : 17:22:13
hello,

I'm not sure how to do it. It's just a simple example. You may use Joins, but I'm looking for another technic.


Table1

ID.....name.......date
123....doccer......null
124....doccer.pdf......null
125....doccer22.pdf......null


What I want to do is to go through all rows in Table 1...To each row in Table 1 belongs another row in Table 1. Search criteria is the name. the difference the prefix ".pdf".

What I want as result the data of table 1 and the belonging row in one row.

ID.....name.......date......ID1.....name1.......date1
123....doccer......null.....124....doccer.pdf......null


That's my attempt:

select * from table1 A1

Union all

Select ID as ID1, name as name1, date as date1 from table1 where A1.name = substring(name,0,len(name)-4)

Ok, it didn't work, but is this the right idea?

Kind regards,

lara

bitsmed
Aged Yak Warrior

545 Posts
Posted - 2012-08-11 : 18:00:32
Hi,

I don't see how this can be done without using join (looking forward to see if any of you guys have an solution not using join).

Anyway, using join, you can try the following:

select *
  from table1 as a
       left outer join (select id as id1
                              ,name as name1
                              ,date as date1
                              ,upper(case
                                        when patindex('%.%',reverse(name))=0 then name
                                        else substring(name,1,length(name)-patindex('%.%',reverse(name))-1)
                                     end
                                    ) as trimmedname
                          from table1
                       ) as b
                    on b.trimmedname=upper(a.name)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-11 : 19:51:30
[code]
select MAX(CASE WHEN Seq=1 THEN ID END) AS ID,
MAX(CASE WHEN Seq=1 THEN name END) AS name,
MAX(CASE WHEN Seq=1 THEN date END) AS date,
MAX(CASE WHEN Seq=2 THEN ID END) AS ID1,
MAX(CASE WHEN Seq=2 THEN name END) AS name1,
MAX(CASE WHEN Seq=2 THEN date END) AS date1
from
(
select row_number() over (partition by left(name,charindex('.',name+'.')-1) order by name) AS Seq,left(name,charindex('.',name+'.')-1) AS trimmedname,*
from table
)t
group by trimmedname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-13 : 13:19:50
Hello to both,

Thanks for your answer.Bitsmed... I am not sure whether I can use it in my example.

@Visakh: definetely I learned a new function (over ..partition) I read alot to it and the simple examples are easy to understand. Sorry, for be so stupid, but I don't get you example.
But I tried it...maybe I join is possible as well, but I don't see it..maybe someone else:

Ok, that's my original query:

SELECT
ID,
Max(Name) as Name,

FROM 


(select * from dee A1, data A2 where A1.DataID in (select DataID from ancestors where AncestorID ='481176') AND A1.Subtype = '399' AND (Select Name from dee where DataID = A1.ParentID) <>'Retired' AND A1.DataID = A2.ID) as test

Group BY ID


Don't be to confused with all the ID's and so on...the result is always the following table:

ID.....name
123....doccer.docx
124....doccer2.docx

in my table dee A1 is one entry with the name doccer.pdf...so the name is similar but the prefix is pdf instead of docx
I need to find that belonging entry..

here is my attempt:

SELECT
MAX(CASE WHEN Seq=1 THEN ID END) AS ID,
Max(CASE WHEN Seq=1 THEN name END) AS Name,
MAX(CASE WHEN Seq=2 THEN ID END) AS ID1,
Max(CASE WHEN Seq=2 THEN name END) AS Name1


FROM 


(select * from dee A1, data A2 where A1.DataID in (select DataID from ancestors where AncestorID ='481176') AND A1.Subtype = '399' AND (Select Name from dee where DataID = A1.ParentID) <>'Retired' AND A1.DataID = A2.ID) as test

Group BY ID



I really struggle to place the over function, because I haven't got only one select...would you be so kind to help me? Can you explain why you use "Seq=1" and "Seq=2"...would be great to understand it right



this is my SQL to search for the right name..I change docx to pdf

select * from dee where name = substring(Name,0,charindex('.', Name))+'.pdf'


Maybe a join is possible..do you see it..of course I would prefer it:) I hope I explained it not too bad and hope we can discuss it...

Kind regards,

Lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-13 : 13:41:11
[code]
;With CTE
AS
(
SELECT
ID,
Max(Name) as Name
FROM dee A1
inner join data A2
on A1.DataID = A2.ID
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='481176'
inner join dee A11
ON A11.DataID = A1.ParentID
where A1.Subtype = '399'
AND A11.Name <>'Retired'
Group BY ID
)

SELECT MAX(CASE WHEN Seq=1 THEN ID END) AS ID,
MAX(CASE WHEN Seq=1 THEN name END) AS name,
MAX(CASE WHEN Seq=1 THEN date END) AS date,
MAX(CASE WHEN Seq=2 THEN ID END) AS ID1,
MAX(CASE WHEN Seq=2 THEN name END) AS name1,
MAX(CASE WHEN Seq=2 THEN date END) AS date1
FROM
(
SELECT row_number() over (partition by left(name,charindex('.',name+'.')-1) order by name) AS Seq,
left(name,charindex('.',name+'.')-1) AS trimmedname, *
FROM CTE
)t
GROUP BY trimmedname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-13 : 18:27:29
Hi,

I understand...you create a cte and then you'll do the over partition...sorry for being so stupid guys..I've been trying it for 90 minutes but I can't see it.
Something is wring with the CTE

;With CTE
AS
(
SELECT
ID,
Max(Name) as Name

FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='481176'
inner join dee A11
ON A11.DataID = A1.ParentID



Group BY ID
)

select * from cte


If I add this

inner join dtree A11
ON A11.DataID = A1.ParentID

I get the error:

Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'Name'.

OK, the columname is not correct defined or now sql server doesn't know..where to get from

I tried to fix it

];With CTE
AS
(
SELECT
A1.ID,
Max(A1.Name) as Name

FROM  dtree A1


but it's not right..do you see what's the problem?

I really have to sy thanks...especialle for your patience...

Kind regards,

Lara
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-13 : 19:34:50
Everthing is alright now...I am sure I will have questions to the cross partition...

kind regards,

lara
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-13 : 19:58:51
Hi,

sorry, I really struggle with the over partition..

ok the CTE is alright:

;With CTE
AS
(
SELECT
ID,
name = Max(A1.Name) 

FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='481177'
inner join dee A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)






The CTE generates the following table:

ID.....name
123....doccer.docx



OK, and now the crucial part..somewhere in the table dee A1 is a file with the same name "doccer" just the prefix is different it's "doccer.pdf"

for that reason the name must be converted with:

substring(Name,0,charindex('.', Name))+'.pdf'


that we can search for it....

I don't know excactly what you are doing here:

SELECT MAX(CASE WHEN Seq=1 THEN ID END) AS ID,
MAX(CASE WHEN Seq=1 THEN name END) AS name,
MAX(CASE WHEN Seq=2 THEN ID END) AS ID1,
MAX(CASE WHEN Seq=2 THEN name END) AS name1,

FROM
(
SELECT row_number() over (partition by left(name,charindex('.',name+'.')-1) order by name) AS Seq,
left(name,charindex('.',name+'.')-1) AS trimmedname, *
FROM CTE
)t
GROUP BY trimmedname


I get an error.


Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near 't'.


How can SeQ=2 be true? Must there not the convertion of the name?that part is really tricky for and you hope you can help me...especially to understand....

Kind regards,

Lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-13 : 23:49:16
you've an extra ,

SELECT MAX(CASE WHEN Seq=1 THEN ID END) AS ID,
MAX(CASE WHEN Seq=1 THEN name END) AS name,
MAX(CASE WHEN Seq=2 THEN ID END) AS ID1,
MAX(CASE WHEN Seq=2 THEN name END) AS name1,
FROM
(
SELECT row_number() over (partition by left(name,charindex('.',name+'.')-1) order by name) AS Seq,
left(name,charindex('.',name+'.')-1) AS trimmedname, *
FROM CTE
)t
GROUP BY trimmedname


seq will be sequential number generated based on name alphabetically sorted for each similar pattern. so in your example doccer group will have 2 records
doccer.docx with seq=1 and doccer2.docx with seq=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-14 : 17:38:34
Hello Visakh16,

thanks for you help..it's not exactly what I was looking for, but look how it worked:

;With CTE
AS
(
SELECT
ID,
name = Max(A1.Name)


FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='480972'
inner join dee A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)


That was the CTE...

Know how I look for the belonging PDF with a simple join...

select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name
	MAX(A2.DataID) as ID1,
	MAX(A2.Name) as Name1
	

	
from CTE A1, dee A2, data A3   

where A2.DataID in (select DataID from ancestors where AncestorID ='480851') 


AND

A2.DataID = A3.ID


AND

A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 


group by A3.ID



works great except in one case..maybe you see it...if I don't find a belonging pdf file (second part of the code) then the whole row will not displayed as result...
the first part from the CTE schould be shown always, regardless if there is a pdf...

hopefully it's not too bad explained..if you need more information..just give me a signal..I hope you can help me

kind regards,

lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-14 : 17:55:48
you're not using it the way i suggested. i dont have any joins in last select. i dont know why you want in the first place. Unless you give us full picture I dont think I will be able to help you out.

I gave suggestion as per your original sample data below


ID.....name
123....doccer.docx
124....doccer2.docx


and i dont even know what you actual query is doing. i just gave CTE with it as you told it gives you starting point

so best thing would be to give proper sample data covering all scenarios and then explaining what you expect as output. otherwise we will still continue with guess work for long

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-14 : 18:22:57
Alright...the cte is perfect...in addition you suggested a different sql..that's true...but i think i explaation was too bad I think..alright:

Table1 (dee)

ID.....name....... type
123....doccer.docx........1
124....doccer.pdf.........2
125....doccer22.docx......1


to each docx file belongs a pdf...
in my example, it's just one pair...
doccer22 has no belonging pdf

here the cte

;With CTE
AS
(
SELECT
ID,
name = Max(A1.Name)


FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='480972'
inner join dee A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)



it's a bit more complex than my example...the result is a list of all docx files in table1


ID.....name.......
123....doccer.docx........
125....doccer22.docx......

that's fine so far..don't care about the cte...it's right..

now I want to go through my cte result list and find to each docx the belonging pdf and write id, name of both files in one row...

here the sql:



select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name
	MAX(A2.ID) as ID1,
	MAX(A2.Name) as Name1
	

	
from CTE A1, dee A2 

where A2.DataID in (select DataID from ancestors where AncestorID ='480851') 


AND

A2.DataID = A3.ID


AND

A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 


group by A3.ID






as you can see..I replase the prefix docx through pdf and seach for the pdf file name in table 1 (dee)

it works great..

here the problem, and it's because of the join..if I can't find a pdf then the docx will not listed as well..in my example "doccer22.docx" would be not listed in my final result table, because no pdf exists...but I want that "doccer22.docx" is listed as well...

the result would be just:

ID.....name....... .....ID1.....name1
123....doccer.docx......124....doccer.pdf



doccer22.docx is not listed..but it schould...hopefully it's better explained..I tried to change the where clause..just that i get this result:


ID.....name....... .....ID1.....name1
123....doccer.docx......124....doccer.pdf
125....doccer22.docx.....null...null

I really appreaciate your patience...thanks...

lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-14 : 18:40:31
[code]
...
select
MAX(A1.ID) as ID,
MAX(A1.Name) as Name
MAX(A2.ID) as ID1,
MAX(A2.Name) as Name1



from CTE A1
left join dee A2
on A2.DataID = A3.ID
AND A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf'
where A2.DataID in (select DataID from ancestors where AncestorID ='480851')


group by A3.ID

[/code]
I hope A3 is a typo as i cant see a table being referred by alias A3 here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-14 : 19:26:45
Hi Visakh16,

oh yes you are right...a left join...it's very late in europe...

I rewrote the code slightly:

...
select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name
	MAX(A2.ID) as ID1,
	MAX(A2.Name) as Name1
	

	
from CTE A1
left join dee A2 
on A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 
where A2.DataID in (select DataID from ancestors where AncestorID ='480851')


but I get the error:

Incorrect syntax near the keyword 'from'.

are such left joins:

on A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf'

not possible? the only connection between CTE A1 and dee A2 is the name...



Yes, in generell there is a third table:

here is the complete code...



select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name, 
	MAX(A1.ParentID) as ParentID,
	MAX(A1.Function1) as Function1,
	MAX(A1.DocumentNumber) as DocumentNumber,
	MAX(A2.DataID) as ID1,
	MAX(A2.Name) as Name1,
	MAX(A2.ParentID) as ParentID1,
	MAX(Case WHEN A3.AttrID = '2' AND A3.DefID = '480853' THEN A3.ValStr END) as DocumentNumber1

	
	
	
	

from CTE A1, dee A2, data A3   

where (A2.DataID in (select DataID from dtreeancestors where AncestorID ='480851') 


AND

A2.DataID = A3.ID


AND

A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 



) 



group by A3.ID



maybe you can help me to rewrite slightly with a left join..sorry for bothering you so much...but it's deep in the night here but i can't sleep without a running sql:) I try it as well:)

kind regards,

lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-14 : 19:36:33
[code]
select
MAX(A1.ID) as ID,
MAX(A1.Name) as Name,
MAX(A1.ParentID) as ParentID,
MAX(A1.Function1) as Function1,
MAX(A1.DocumentNumber) as DocumentNumber,
MAX(A2.DataID) as ID1,
MAX(A2.Name) as Name1,
MAX(A2.ParentID) as ParentID1,
MAX(Case WHEN A3.AttrID = '2' AND A3.DefID = '480853' THEN A3.ValStr END) as DocumentNumber1
from CTE A1
left join dee A2
on A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf'
left join data A3
on A2.DataID = A3.ID
where A2.DataID in (select DataID from dtreeancestors where AncestorID ='480851')
group by A3.ID
[/code]

seriously I dont know what you're trying to do here as A3.ID wont always have a value due to LEFT JOIN and it doesnt make any sense to group on it

But I'll refrain myself from thinking too much on your requirement as you dont seem to be interested in letting us know the full picture so far!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-14 : 20:59:39
Hi,

thanks...as you asumed..it's not working correctly...I think I will explain the whole situation with 3 tables...I thought maybe it's too complex for the readers, but I think there isn't another way...

once more..to keep it simple..I've been trying it for over 1 hour and don't get it..



select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name, 
	MAX(A1.ParentID) as ParentID,
	MAX(A1.Function1) as Function1,
	MAX(A1.DocumentNumber) as DocumentNumber,
	MAX(A2.DataID) as ID1,
	MAX(A2.Name) as Name1,
	MAX(A2.ParentID) as ParentID1



from CTE A1
left join dee A2
on A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 

where A2.DataID in (select DataID from ancestors where AncestorID ='480851')  
group BY A1.ID



it's just a simple left join...it seems the left join doesn't work...every time if I can't find a beloniing pdf at this point:

left join dee A2
on A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf'


the left site..(everthing in table CTE A1) is not shown...I've ssen so many samples...the table is exactly filled as I described...have you got a theoreticially idea...it's so weird...I think, maybe my groung is wrong..but grouping by name has the same effect...;(

Kind regards,

Lara
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-16 : 10:31:47
Hi,

now I send you the complete scenario..I hope it's not too much:



I' ve got a table (dee) with documents:



ID.........name...........ParentID
489213.....doc11.pdf.......480851
492565.....doc2.pdf.......480851
481186.....doc1.xls......481177
492345.....doc2.doc......480518


This is the CTE:

;With CTE
AS
(
SELECT
ID,
name = Max(A1.Name),
ParentID = MAX(A1.ParentID),
MAX(CASE 
WHEN A2.AttrID = '2' AND A2.DefID = '480523' THEN A2.Valstr END) as Function1,
MAX(Case
WHEN A2.AttrID = '2' AND A2.DefID = '480853' THEN A2.ValStr END) as DocumentNumber 

FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='480972'
inner join dee A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)

select * from CTE;



The CTE generates a the following table:



ID.......name...................ParentID.........Function1......DocumentNumber
481189...doc1.xls...............481177....	NULL....	1234
492345...doc2.doc...............480518....	NULL....	ewe




It's a list of all Office Documents which are located in one folder somewhere. The AcestorID is the ID of the folder.


So far everthing is fine.

to each office document in the cte table belongs a pdf document, which is to find in table dee. Key is the column "name". In my example to doc1.xlsx belongs doc1.pdf
That's why I convert the name in my sql...

alright..what I want is a list...of all office documents..that's now the crucial part...in some case to the office document doesn't exist a pdf document...in that case I want the office Document still in the result list (left join)...

This is the code to generate the list of documents:



select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name, 
	MAX(A1.ParentID) as ParentID,
	MAX(A1.Function1) as Function1,
	MAX(A1.DocumentNumber) as DocumentNumber,
	MAX(A2.DataID) as ID1,
	MAX(A2.Name) as Name1,
	MAX(A2.ParentID) as ParentID1
	


from CTE A1 

left join dee A2 

on A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 

group by A1.ID



the result:




ID.......name........ParentID..Function1..DocumentNumber....ID1.......name1........ParentID1
481186...doc1.xls.....481177...NULL.......1234...............493335...doc1.pdf...493225
492345...doc2.doc.....480518...NULL........ewe...............493451...doc2.pdf...493225



the result are not right...for doc1.xls a pdf can't be found because it's not in the table...as you can see from the original table..the parentid of the pdf is wrong...I don't know where it's coming from...

I hope you can help me..if you need more..you tell me:)

the real scenario is with 3 tables, but I think we should start with two..it seems sql writes my search name as result?

Kind regards,

Lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-16 : 14:37:08
change join like


...
from CTE A1 

left join dee A2 

on A2.name = left(A1.Name,charindex('.', A1.Name)-1)+'.pdf'




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-16 : 18:23:37
Hi,

It's still the same result...anyway..we should not spend to much effort...
The following would be OK as well....I only want the office documents as result to which no PDF belongs...that one schould be easier...

so I geht all Documents with teir belonging PDF


;With CTE
AS
(
SELECT
ID,
name = Max(A1.Name),
ParentID = MAX(A1.ParentID),
MAX(CASE 
WHEN A2.AttrID = '2' AND A2.DefID = '480523' THEN A2.Valstr END) as Function1,
MAX(Case
WHEN A2.AttrID = '2' AND A2.DefID = '480853' THEN A2.ValStr END) as DocumentNumber 

FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='480972'
inner join dtree A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)




select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name, 
	MAX(A1.ParentID) as ParentID,
	MAX(A1.Function1) as Function1,
	MAX(A1.DocumentNumber) as DocumentNumber,
	MAX(A2.DataID) as ID1,
	MAX(A2.Name) as Name1,
	MAX(A2.ParentID) as ParentID1
	



from CTE A1, dee A2 

where (A2.DataID in (select DataID from ancestors where AncestorID ='480851') 


AND

A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 



)
group by A1.ID





What I tried. That statement aboves give me alle document to which a PDF could be found...now we just have to negate the query..

I just change this part:


AND

NOT EXISTS (A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' )


but it doesn't work..Am i on the right way?

Kind regards,


Lara
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-17 : 10:11:43
Sorry unless I know what exactly you are trying to do I wont be able to suggest anything. can you explain the relevance of first query within CTE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

musclebreast
Yak Posting Veteran

77 Posts
Posted - 2012-08-17 : 16:54:49
Hi VisaKH16,

ok, I try to explain it better:)

I' ve got a table (dee) with documents:


ID.........name...........ParentID
489213.....doc11.pdf.......480851
492565.....doc2.pdf.......480851
481186.....doc1.xls......481177
492345.....doc2.doc......480518


The documents are located in different foldern. That's why I need the CTE...The CTE gives me a list of all Office documents (xls,doc) of one folder. I need to filter these with the CTE. The Ancestor ID is the folder in which the office documents should be located.

;With CTE
AS
(
SELECT
ID,
name = Max(A1.Name),
ParentID = MAX(A1.ParentID),
MAX(CASE 
WHEN A2.AttrID = '2' AND A2.DefID = '480523' THEN A2.Valstr END) as Function1,
MAX(Case
WHEN A2.AttrID = '2' AND A2.DefID = '480853' THEN A2.ValStr END) as DocumentNumber 

FROM  dee A1
inner join data A2 
on A1.DataID = A2.ID
AND A1.Subtype = '144' 
inner join ancestors an
ON an.DataID = A1.DataID
and an.AncestorID ='480972'
inner join dtree A11
ON A11.DataID = A1.ParentID

where A11.Name <>'Retired' 



Group BY ID
)


OK, what I want to do.

to each office document in the cte table belongs a pdf document, which is to find in the table dee as well. Key is the column "name". In my example to doc1.xlsx belongs doc1.pdf
That's why I convert the name in my sql...


This is the code to generate the list of documents Office documents to which a PDF document was found:






select 
	MAX(A1.ID) as ID,
	MAX(A1.Name) as Name, 
	MAX(A1.ParentID) as ParentID,
	MAX(A1.Function1) as Function1,
	MAX(A1.DocumentNumber) as DocumentNumber,
	MAX(A2.DataID) as ID1,
	MAX(A2.Name) as Name1,
	MAX(A2.ParentID) as ParentID1
	



from CTE A1, dee A2 

where (A2.DataID in (select DataID from ancestors where AncestorID ='480851') 


AND

A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf' 



)
group by A1.ID






The SQL goes through the CTE table and checks whether there is a belonging PDF in the an other folder.

The whole code is working perfectly.

But What I want now, is only a list of office documents from the cte to which no pdf could be found.

Logicially it must be something like that:


(A2.name = substring(A1.Name,0,charindex('.', A1.Name))+'.pdf') = false

we just need to negate that part I think...I tried it, but is that possible in SQL?

I hope you can understand my problem other just ask me..:)


another theoreticially solution: I've got the list from the cte and the list of documents to which a pdf exists....no I compare both list and exclude all documents which are in both tables...

Kind regards,

Lara
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site