Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Database and Reporting (http://www.xtremevbtalk.com/-net-database-and-reporting/)
-   -   CASE help I think!! (http://www.xtremevbtalk.com/-net-database-and-reporting/327369-help.html)

lidds 10-30-2014 07:01 AM

CASE help I think!!
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


dotnetwrassler 11-02-2014 04:16 PM

Re: Ways of SELECT-ing (gather data) from multiple tables
A don't have a "definitive" answer, because in the SQL world there are many ways to do things (and I'm not a database expert),
but my first thought in reading what you are trying to do is:
have you researched using JOIN (inner or outer, I'm not sure,
depending on the details/specifics of how the data is organized internally to the tables).

You should also be able to do a SELECT WHERE on multiple tables, though,
either with, or without, using JOIN (but maybe including GROUP BY).

This "Using multiple tables in a query" article my be helpful to glance through,
since it shows using SELECT with and without JOIN for multiple tables.

There is so much coding information of SQL JOINs out there, but its easy to get lost in all the specific examples for specific queries.

To understand them conceptually these pages (1, 2 - scroll to page bottom, 3) have some nice diagrams.

As regards to CASE, you haven't shown how you were thinking of using it,
but a lot of times people unfamiliar with its use in SQL try to use it as a "control of flow statement" when its actually an SQL expression.
Some example links (1, 2).

I've heard that CASE queries can be worrisome from a performance tuning standpoint (especially if they involve correlated subqueries),
and it says as a caveat in this TechNet article (near the end of the article):

Using simplified CASE expression statements results to have cleaner code and speed up development time,
but they show poor performance in some situations.
So if we are in performance tuning phase of software development,
itís better to think about alternative solutions.

JustinCase2 02-24-2015 07:19 AM

If you're doing more queries than this you really should read up a bit on SQL and joining tables, it's not that complicated and highly useful. :)

Anyway, for this one it would be quite simple:


SELECT d.DocumentNo, o.OwningDepartment FROM documentsTbl d LEFT OUTER JOIN docOwnerTbl  o ON (d.Department1 = o.Department1) AND (d.Department2 = o.Department2)
What i do is, i give both the tables an alias (documentsTbl is d and docOwnerTbl is o). I don't really need to do that part, it's just for convenience. I could have written the full table name each time instead. Then, I join the tables, by specifying the 2 columns that need to mach (ON statement). LEFT OUTER JOIN meens that from the table on the left (the first one specified in the FROM clause), we want ALL records returned, even if we find no match in the second table. The value for the OwningDepartment Field will then be Null.
An INNER JOIN would have only returned the records that have matches (INNER joins are not LEFT nor RIGHT).

All times are GMT -6. The time now is 07:43 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.