CASE help I think!!

[lidds]

I need some help with this as I am not majorly SQL knowledgable and this has got me stuck at the point I do not know where to start, so hopefully someone could assist.

I have a table called "documentsTbl" which has the following stucture and example data in it

DocumentNo | Department1 | Department2
-------------------------------------------------
12345 | Accounts | Sales
12346 | Finance | Sales
12347 | Admin |Finance

I then have another table called "docOwnerTbl" which has the following structure and example data in it

Department1 | Department2 | OwningDepartment
--------------------------------------------------------
Accounts | Sales | Accounts
Finance | Sales | Sales
Admin | Finance |Admin

Basically "documentsTbl" holds a list of documents and a department 1 and 2 column. I then have another table called "docOwnerTbl" which lists a matrix type data which I want to use to determine which department owns the document. An example of what I mean is document 12346 has department1 as Finance and department2 as Sales, if you look at the docOwnerTbl you will see that if department1 is Finance and department2 is Sales then the "OwningDepartment" is Sales.

What I want to do is have a SELECT query that will return the "OwningDepartment" for each document in documentTbl that match the criteria in the "docOwnerTbl"

FYI: I am doing this in MS SQL Server 2008

I hope that makes sense?

Thanks in advance

Simon

 

[IanRyder]

Hi,

This is not a Case issue as you intimate and what you need to do is to create an Inner Join relationship between the two tables across the two fields in question. Have a play with this example:-

SELECT dbo.documentsTbl.DocumentNo, dbo.docOwnerTbl.OwningDepartment
FROM dbo.documentsTbl INNER JOIN
            dbo.docOwnerTbl ON dbo.documentsTbl.Department1 = dbo.docOwnerTbl.Department1 AND dbo.documentsTbl.Department2 = dbo.docOwnerTbl.Department2

Hope that helps.

Cheers,

Ian