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 |
|
cbarrett
Starting Member
1 Post |
 Posted - 2011-08-09 : 13:01:45
|
| Here is a basic outline of what tables-columns I have:====================================================== CONTACT -contact_PK -contactName CLIENT_CONTACT -FK_contact_PK -FK_client_PK CLIENT -client_PK -FK_Employee1_PK -FK_Employee2_PK -FK_Employee3_PK MATTER -matter_pk -FK_client_PK -FK_Employee1_PK -FK_Employee2_PK -FK_Employee3_PK EMPLOYEE -Employee_PK -EmployeeName======================================================And what I'm trying to figure out is to how to create query like this: contactName list of Unique Employee Names |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-09 : 13:24:57
|
| whats the significance of matter here?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-09 : 15:53:08
|
| Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. >> Here is a basic outline of what tables-columns I have: <<This is a vague, total mess. You did not get one data helmet name correct! You actually put “pk” and “fk” on the names! Singular table names! Can use you as a bad example in a book? N one will believe it if I do not actually show them you really posted this. Contacts with clients have no date or time? Let's look just one of your narratives and try to turn it into a real table:Client-client_pk-fk_employee1_pk-fk_employee2_pk-fk_employee3_pkWow! Only one client; business stinks! Table names are collective or plural because they model sets. The affix “pk” is HOW a data element is used; the ISO-11179 rules require that you name it for what it is. This is followed by a repeating group. A total violation of First Normal Form (1NF)! A fake array in SQL! Why do you think that one of our employees is an attribute of a client? NO! That is silly on the face of it. A client is an entity; an employee is a separate entity. They have a relationship. CREATE TABLE Personnel_Client_Assignments (client_duns CHAR(10) NOT NULL, emp_id CHAR(10) NOT NULL REFERENCES Personnel(emp_id) ON UPDATE CASCADE ON DELETE CASCADE,PRIMARY KEY (client_duns, emp_id));I assumed the clients are businesses or individuals with a DUNS (if you do not know that that is, why are you writing business software?) I also assume that you do not want a limit on the number of employees assigned to a client. Feel embarrassed enough to educate yourself? Let's try to fix it, if you actually care and are not just looking for someone to do your job or homework for you. while you go read the Basic Netiquette at the front of this forum so you will not do this again. Learn and try again. --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 |
|
|
|
|
|
|
|
|
Help with a query