CASE help I think!!
CASE help I think!!
CASE help I think!!
CASE help I think!!
CASE help I think!!
CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!!
CASE help I think!! CASE help I think!!
CASE help I think!!
Go Back  Xtreme Visual Basic Talk > > > CASE help I think!!


Reply
 
Thread Tools Display Modes
  #1  
Old 10-30-2014, 08:01 AM
lidds lidds is offline
Centurion
 
Join Date: May 2004
Posts: 100
Default 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

Simon
Reply With Quote
  #2  
Old 11-02-2014, 05:16 PM
dotnetwrassler dotnetwrassler is offline
Regular
 
Join Date: Sep 2014
Location: USA (Pacific/West Coast)
Posts: 71
Default 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):
Quote:
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.

Last edited by dotnetwrassler; 11-02-2014 at 05:49 PM.
Reply With Quote
  #3  
Old 02-24-2015, 08:19 AM
JustinCase2 JustinCase2 is offline
Junior Contributor
 
Join Date: Apr 2006
Posts: 324
Default So..

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:

Code:
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).

Last edited by JustinCase2; 02-24-2015 at 08:59 AM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
CASE help I think!!
CASE help I think!!
CASE help I think!! CASE help I think!!
CASE help I think!!
CASE help I think!!
CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!! CASE help I think!!
CASE help I think!!
CASE help I think!!
 
CASE help I think!!
CASE help I think!!
 
-->