Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JohnShell
Starting Member
4 Posts |
 Posted - 2011-02-21 : 15:02:03
|
| Hi,Need with with query. Have 4 tables in db. Tables as follows - fields in parenthesis:Emp (name, team, branch)TL (name, team, branch) - TL stands for team lead - about 12 TLsBC (name, branch) - BC stands for branch chief - 3 BCsCore (name - also has other non-relevant/related fields)User makes request - user's name gained from another query via ColdFusion code and housed in a CF grid. From user's name need to determine TL and BC names.If user is TL then just need BC name.Using TL and BC names to direct email to those folks.Are aliases or joins needed? How do I do this query? Do I need several queries or just one complex query?Any help is deeply appreciated.Thanks - John |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-02-21 : 19:08:41
|
| >> Have 4 tables in db. Tables as follows - fields [sic] in parenthesis: <<Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.htmlSince you did not know that columns are nothing like fields, or how to name a table, you might want to read a copy of the simplest intro book I know -- http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905Let me try to clean up the vague, rude mess you posted. Looking at the one “Emp” you had (singular table names?), did you mean something like this? CREATE TABLE Personnel(emp_name VARCHAR (25) NOT NULL PRIMARY KEY, team_name VARCHAR (25) NOT NULL, branch_name VARCHAR (25) NOT NULL);Besides the incorrect data elements names, you think a team is an attribute of a person. Wrong, fundamentally wrong. A team is an entity. Team membership is a relationship between the personnel and the teams. Where is that table? >> User makes request - user's name gained from another query via ColdFusion code and housed in a CF grid. <<Where is the users table? Why does SQL care about a “CF grid”? >> From user's name need to determine TL and BC names. If user is a Team Leader then just need Branch Chief name.<<How are the user matches determined? Where is that email you wanted to use? Let's start over with usable DDL and minimal Netiquette.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
 |
|
|
JohnShell
Starting Member
4 Posts |
Posted - 2011-02-22 : 08:07:53
|
| Never mind. How rude of me, at least, according to you. Perish the thought that I've offended your sensibilities. I am not interested in your didatic scolding as I only wanted some help with my problem. You have effectively poisoned my attitude toward this sight and specifically to your arrogance and doctrinaire attitude. Apparently your are unfamiliar with the philosophy of snaring more flies with honey than vinegar.Good day you boorish person. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-22 : 12:27:39
|
| Well John it would be better if you could come up with sample data for the specified table names and at last the desired output in tabular format ... Also, let us know the relationship between these tables e.g. Primary and Foreign keys relationship. This will help us/me to help you in writing the required query or to give you at least a right direction ... CheersMIK |
|
|
|
JohnShell
Starting Member
4 Posts |
Posted - 2011-02-22 : 18:38:40
|
| MIK,Thank you for your post. I am unable to comply until tomorrow when I return to work. At that time I will again have access to my database where I can exhibit the required information. My churlish reply to jcelko was in response to this comment, "Let me try to clean up the vague, rude mess you posted."JS |
|
|
|
|
|
|
|
|
Query help