Outputting Results from complicated database structure (SQL Server)

Question

This will be a long question so I'll try and explain it as best as I can.

I've developed a simple reporting tool in which a number of results are stored and given a report id, these results were generated from a particular quote being used on the main system, with a huge list of these being stored in a quotes table. Here are the current batch:

REPORTS

REP_ID    DESC            QUOTE_ID
-----------------------------------
1         Test            1
2         Today           1
3         Last Week       2

RESULTS

RES_ID    TITLE           REFERENCE          REP_ID
---------------------------------------------------
1         Equipment       Toby               1
2         Inventory       Carl               1
3         Stocks          Guest              2
4         Portfolios      Guest              3

QUOTE

QUOTE_ID     QUOTE
------------------------------------
1             Booking a meeting room
2             Car Park Policy
3             New User Guide

So far, so good, a simple stored procedure was able to pull all the information necessary.

Now, the feature list has been upped to include categories and groups of the quotes. In the Reports table quote_id has been changed to group_id to link to the following tables.

REPORTS
- REPORT_ID
- DESC
- GROUP_ID

GROUP
 - GROUP_ID
 - GROUP

GROUP_CAT_JOIN
 - GCJ_ID
 - CAT_ID
 - GROUP_ID

CATEGORIES
 - CAT_ID
 - CATEGORY

CAT_QUOTE_JOIN
 - CQJ_ID
 - CAT_ID
 - QUOTE_ID

The idea of these changes is so that instead of running a report on a quote I should now write a report for a group where a group is a set of quotes for certain occasions. I should also be able to run a report on a category where a category is also a set of quotes for certain departments. The trick is that several categories can fall into one group.

To explain it further, the results table has a report_id that links to reports, reports has a group_id that links to groups, groups and categories are linked through a group_cat_join table, the same with categories and quotes through a cat_quote_join table.

In basic terms I should be able to pull all the results from either a group of quotes or a category of quotes. The query will aim to pull all the results from a certain report under either a certain category, a group or both. This puzzle has left me stumped for days now as inner joins don't appear to be working and I'm struggling to find other ways to solve the problem using SQL.

Can anyone here help me?

---

Here's some extra clarification.

I want to be able to return all the results within a category, but as of right now the solution below and the ones I've tried always output every solution within a description, which is not what I want.

Here's an example of the data I have in there at the moment

Results

RES_ID    TITLE           REFERENCE          REP_ID
---------------------------------------------------
1         Equipment       Toby               1
2         Inventory       Carl               1
3         Stocks          Guest              2
4         Portfolios      Guest              3

Reports

REP_ID    DESC            GROUP_ID
-----------------------------------
1         Test            1
2         Today           1
3         Last Week       2

GROUP

GROUP_ID     GROUP
---------------------------------
1            Standard
2            Target Week

GROUP_CAT_JOIN

GCJ_ID       GROUP_ID     CAT_ID
----------------------------------
1            1            1
2            1            2
3            2            3

CATEGORIES

CAT_ID        CAT
-------------------------------
1             York Office
2             Glasgow Office
3             Aberdeen Office

CAT_QUOTE_JOIN

CQJ_ID       CAT_ID      QUOTE_ID
-----------------------------------
1            1           1
2            2           2
3            3           3

QUOTE

QUOTE_ID     QUOTE
------------------------------------
1             Booking a meeting room
2             Car Park Policy
3             New User Guide

This is the test data I am using at the moment and to my knowledge it is similar to what will be run through once this is done. In all honesty I'm still trying to get my head around this structure.

The result I am looking for is if I choose to search by group I'll get everything within a group, if I choose everything inside a category I get everything just inside that category, and if I choose something from a category in a group I get everything inside that category. The problem at the moment is that whenever the group is referenced everything inside every category that's linked to the group is pulled.

Answer 1

Is this what you are looking for?

SELECT a.* 
FROM Results a
JOIN Reports b ON a.REP_Id = c.REP_Id
WHERE EXISTS (
    SELECT * FROM CAT_QUOTE_JOIN c
    WHERE c.QUOTE_ID = b.QUOTE_ID -- correlation to the outer query
      AND c.CAT_ID = @CAT_ID -- parameterization
    )
   OR EXISTS (
    -- note that subquery table aliases are not visible to other subqueries
    -- so we can reuse the same letters
    SELECT * FROM CAT_QUOTE_JOIN c, GROUP_CAT_JOIN d 
    WHERE c.CAT_ID = d.CAT_ID -- subquery join
      AND c.QUOTE_ID = b.QUOTE_ID -- correlation to the outer query
      AND d.GROUP_ID = @GROUP_ID -- parameterization
    )

Answer 2

The following will get the necessary rows from the results:

select
    a.*
from
    results a
    inner join reports b on
        a.rep_id = b.rep_id
        and (-1 = @GroupID or 
            b.group_id = @GroupID)
        and (-1 = @CatID or
            b.cat_id = @CatID)

Note that I used -1 as the placeholder for all Groups and Categories. Obviously, use a value that makes sense to you. However, this way, you can specify a specific group_id or a specific cat_id and get the results that you want.

Additionally, if you want Group/Category/Quote details, you can always append more inner joins to get that info.

Also note that I added the Group_ID and Cat_ID conditions to the Reports table. This would be the SQL necessary if and only if you add a Cat_ID column to the Reports table. I know that your current table structure doesn't support this, but it needs to. Otherwise, as my grandfather used to say, "Boy, you can't get there from here." The issue here is that you want to limit reports by group and category, but reports only knows about group. Therefore, we need to tie something to the category from reports. Otherwise, it will never, ever, ever limit reports by category. The only thing that you can limit by both group and category is quotes. And that doesn't seem to be your requirement.

As an addendum: If you add cat_id to results instead of reports, the join condition should be:

    and (-1 = @CatID or
        a.cat_id = @CatID)