Best way to do this query - SQL Server Forums

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

Best way to do this query

Author

Topic

sartis
Starting Member

11 Posts

Posted - 2015-01-29 : 11:53:15

Hello, I have a database with the following tables:

stakeholders - contains information about people (first name, last name, etc)
contacts - contains information about contacts they have been made by the stakeholders (contact title, contact date, etc)
followups - contains information about actions that need to be taken for the contact (followup title, followup date, etc)

any stakeholder can have numerous contacts

any contact can have numerous followups associated with the contact

I'm trying to create a report that would look like this:

stakeholder
-----contact(this contact has 1 followup)
--------followup
-----contact (this contact has 3 followups)
--------followup
--------followup
--------followup
-----contact (this contact has no followups)

What is the best way to do this query? using sub queries??

Thanks, Steve

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 12:00:00

select * from stakeholders s
join contacts c on s.sid = c.sid
join followups f on c.fid = fid
order by s.name, c.title, c.date, f.title, f.date

sartis
Starting Member

11 Posts

Posted - 2015-01-29 : 13:04:26

quote:
Originally posted by gbritton

select * from stakeholders s
join contacts c on s.sid = c.sid
join followups f on c.fid = fid
order by s.name, c.title, c.date, f.title, f.date

Thanks for this but it seems to return only contacts that have followups associated with a contact. Some contacts do not have followups.

Thanks,

jleitao
Posting Yak Master

100 Posts

Posted - 2015-01-29 : 13:14:33

select
*
from stakeholders s
Left join contacts c
on s.sid = c.sid
Left join followups f
on c.cid = f.cid
order by s.name, c.title, c.date, f.title, f.date

------------------------
PS - Sorry my bad english

sartis
Starting Member

11 Posts

Posted - 2015-01-29 : 14:10:10

quote:
Originally posted by jleitao

select
*
from stakeholders s
Left join contacts c
on s.sid = c.sid
Left join followups f
on c.cid = f.cid
order by s.name, c.title, c.date, f.title, f.date

This is closer but the issue now is if a contact has more than 1 followup it appears as a separate line....here's an example of the way it appears now:

Steve
----contact id 11
---------followup id 1

Steve
----contact id 11
---------followup id 5

and it should be

Steve
----contact id 11
---------followup id 1
---------followup id 5

Thanks,

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 15:41:19

Please post the exact output (SQL does not prepend dashes as in your samples).

Notice since you are doing a left join, you will get nulls for the columns in contacts and follow-ups. Nulls would sort first. is that what you're seeing?

sartis
Starting Member

11 Posts

Posted - 2015-01-29 : 15:55:16

so ignore the dashes, I was just indenting.....yes using this

select
*
from stakeholders s
Left join contacts c
on s.sid = c.sid
Left join followups f
on c.cid = f.cid
order by s.name, c.title, c.date, f.title, f.date

if a contact has more than one followup it appears on another row, I want it to appear under the first row...so like

stakeholder name
contact
all followups

next stakeholder
contact
followup

Thanks,

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-29 : 16:24:22

so, you're getting:

stakeholder1, contact1, followup1
stakeholder1, contact1, followup2
stakeholder2, contact1, followup1

etc. right?

That would be normal, since a query returns a rowset that is analogous to a relation in the formal sense.

What you seem to be after is better done at the application layer working on the output from the query (e.g. Excel excels at this sort of thing).

You can do it in SQL, by using the Row_number function. You'll still get the same columns, but we can use the row numbers to return spaces instead of the stakeholder or contact info.

You could also do it using a CURSOR but I recommend strongly against that route for performance reasons. It turns set logic into iterative row-by-row logic, However, you can probably see right off how this would solve your problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources