CASE help I think!!

lidds

Well-known member
Joined
Oct 19, 2004
Messages
122
Programming Experience
Beginner
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

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
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
 
Top Bottom