March 12, 2008 at 5:29 pm
Help me if you can I'm feeling down....
Hi guys I need help with a query I just can't get my head around.
I have a table with three fields:
ID Main_id Attach_id
all fields are integers and represent serial numbers of emails, the purpose of this table is it allows for the link of emails and their attachments.
If you have one email called 1 and in that email there is another as an attachment called 2 then the relationship is expressed in the table with 1 in the main_id field and 2 in the attach_id field.
Then if email 2 has an attachment in it aswell say email 3 then there will be another record in the table where 2 is now in the main_id field and 3 will be in the attach_id field.
My problem is I want to be able to count all emails that might be attached to any one of the emails so I can't just count the main_id field for occurances of a particular email but I need to count their attachments as well as their attachments of attachments,
My thoughts are to join the table onto itself over and over again but how do I account for an unkown amount of attachments of attachments, how deep do I run.
Thanks
How
March 12, 2008 at 5:54 pm
You need a recursive query, but they make my brain hurt, so I'll have to get back to you after dinner (unless someone else does it first 🙂 )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2008 at 5:56 pm
Try looking at this...
March 12, 2008 at 7:04 pm
You have described your problem, but a solution is still hazy, as I need to see what the actual data looks like. Please post a sample table, with test data.
Additionally, how many levels of attachement can an email have? Is it 2?
March 12, 2008 at 7:08 pm
Thats the thing you just don't know how deep they go. The data looks like this
148354853
248364854
348374855
448384856
548394857
648404858
748414859
848424860
948434861
1048444862
1148454863
1248464864
1348474865
1448484866
1548494867
1648194868
1748224869
1848234870
1948244871
2048254872
March 12, 2008 at 7:14 pm
Assuming you're using SQL 2005 (since this is the SQL 2005 part of the forum), you can use a CTE for this. Books Online has good data on these.
It should end up looking something like:
;with EmailsCTE (MainID, AttachID) as
(select main_id, attach_id
from dbo.emails
where main_id = @inputparameter_in
union all
select main_id, attach_id
from dbo.emails
inner join emailsCTE
on emails.main_id = emailsCTE.attachid)
select *
from emailscte
That should give you all the levels for any given top e-mail.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2008 at 7:15 pm
Oh, and good Beatles references. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2008 at 7:22 pm
Thank you so much I will give it a bash..
Cheers
March 12, 2008 at 8:04 pm
GSquared (3/12/2008)
Oh, and good Beatles references. 🙂
Actually mine was from Month Python. :laugh:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2008 at 10:48 am
rbarryyoung (3/12/2008)
GSquared (3/12/2008)
Oh, and good Beatles references. 🙂Actually mine was from Month Python. :laugh:
"Help me if you can, I'm feeling down
and I do appreciate you're being 'round
Help me get my feet back on the ground
Won't you please, please help me
Help me
Help me" - The Beatles, Help Me
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply