Group By With Inner and Outer Joins

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

SQL Server 2000 Forums

Transact-SQL (2000)

Group By With Inner and Outer Joins

Author

Topic

d473566
Starting Member

23 Posts

Posted - 2002-03-15 : 23:19:22

I have searched the forums and have not seen this asked yet, so hopefully I am not duplicating another post.

Here is the problem. I have one table of records, joined to a pivot table, joined to a table that holds descriptions of that record. (The table of records is also joined to another pivot table, joined to descriptions of funding, but I haven't gone there yet!)

What I want to do is have it output (in Cold Fusion, not that this is a necessary detail) the university name first, then all of the group_names, then all of the desriptive_names.

Is this possible, or do I need to do some funky Cold Fusion programming.

Here is the code I have come up with so far:

SELECT
tbl_benefit_records.id,
tbl_benefit_records.university_id AS univID,
tbl_benefit_types.benefit_name,

tbl_universities.university,

tbl_benefit_groups.group_name,
tbl_descriptives.id AS descriptID,
tbl_descriptives.descriptive_name,
tbl_record_descriptives.additional_info

FROM tbl_benefit_records
INNER JOIN tbl_benefit_types ON tbl_benefit_records.benefit_type_id = tbl_benefit_types.id
INNER JOIN tbl_universities ON tbl_benefit_records.university_id = tbl_universities.id
INNER JOIN tbl_benefit_emp_groups ON tbl_benefit_records.id = tbl_benefit_emp_groups.record_id
INNER JOIN tbl_benefit_groups ON tbl_benefit_groups.id = tbl_benefit_emp_groups.group_id
LEFT OUTER JOIN tbl_record_descriptives ON tbl_record_descriptives.record_id = tbl_benefit_records.id
LEFT JOIN tbl_descriptives ON tbl_descriptives.id = tbl_record_descriptives.benefit_descript_id

And the results:

ID|univID|benefit_name|university|group_name|descriptID|descriptive_name|additional_info
20|2|Medical|U of O|Faculty|29|CPP Integration|cpp test
20|2|Medical|U of O|Faculty|30|Indexing Formula|indexing test
20|2|Medical|U of O|Librarian|29|CPP Integration|cpp test
20|2|Medical|U of O|Librarian|30|Indexing Formula|indexing test

What I want, is the result to be formatted like:

University: U of O
group_names: faculty,librarian
descriptive_name: CPP integration, Indexing Formula

Is this even a GROUP BY issue?
Hope I have explained the problem thoroughly. Thanks for any help!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-16 : 00:14:52

Hi

This is more a reporting thing, it is doable in SQl, you need to build a temp table or 2 and do some funky "coalesce" (search the site for that) -ing of data

You might be better off doing it in Cold Fusion, for every row, look to see if the univerity name is that same as the last and either append to the current line or add a new one.

Damian

d473566
Starting Member

23 Posts

Posted - 2002-03-18 : 13:15:23

That's what I thought, since it was pulling all the info correctly, just not sorting it the way I want. I was hoping for a single query answer. Unfortunately, right now it is 3-4 queries per record to get the info the way I want, which is not very efficient.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources