Including names from one field multiple times

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Including names from one field multiple times

Author

Topic

NiceSituation
Starting Member

22 Posts

Posted - 2008-10-24 : 16:22:09

Hi everyone, I have a problem trying to extract customer, agency, contact and sales representatives information and posting it several times on different fields. I have two tables, A and B. A contains the following fields: Id, Name (which includes the name for all customers, agencies, contacts and sales representatives), Salesrep, Agency, Contact. B contains the following fields: Id, Organization_Number, Account_Number.

A.Salesrep, A.Agency and A.Contact hold the same Id as A.Id, and since A.Name refers to all types of "people", this means that for a customer, A.Salesrep gives you the Id (A.Id) of his sales representative, which also applies to A.Agency and A.Contact for the agency through which the customer makes contact with the organization and the contact person on the customer's side, respectively. For a Sales Representative, all three fields will be left NULL, for an Agency A.Salesrep and A.Contact may or may not be NULL, while A.Agency will be NULL, and for a Contact A.Salesrep may or may not be NULL and the other two will be NULL. Table B holds more information about table A's "people", and through their Ids, both tables can be joined.

The main problem came when I was asked to return all customers, agencies and contacts and their respective sales representatives, agencies and contacts (their names, of course). I need to return these four fields (A.Name, A.Salesrep, A.Agency, A.Contact) as well as B.Organization_Number and B.Account_Number.

Currently, I have the following query:

SELECT DISTINCT TOP 100 PERCENT

B.Organization_Number,
B.Account_Number,
A.Name, --customer, agency, sales representative or contact names

ALIAS1.Name AS Sales_rep, --sales representative for this person
ALIAS2.Name AS Agency, --agency for this person
ALIAS3.Name AS Contact, --contact for this person

FROM
A
INNER JOIN B
ON A.Id = B.Id
INNER JOIN A ALIAS1
ON A.Salesrep = ALIAS1.Salesrep
INNER JOIN A ALIAS2
ON A.Agency = ALIAS2.Agency
INNER JOIN A CUENTA3
ON A.Contact = ALIAS3.Contact

ORDER BY B.Account_Number

However, this returns for each "person" all combinations of A.Salesrep, A.Agency and A.Contact, ignoring those in which any of those fields is NULL. Instead of having a single table with one row per "person", I get loads of rows per person, and only for those where all fields are not NULL.

Any thoughts as to how I can only return the information required? If you need additional information, please let me know.

May today be good, and tomorrow better

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 16:32:17

Hi, show us your original Tables and what output result should look like.

NiceSituation
Starting Member

22 Posts

Posted - 2008-10-24 : 19:30:01

quote:
Originally posted by hanbingl

Hi, show us your original Tables and what output result should look like.

Hi, here is a sample of some rows from the original tables:

Table A

Id Salesrep Name Contact Agency

0010 NULL Sales Team 1 NULL NULL
1037 0010 ABBOTT LABS 1038 0107
1053 0013 ACTINVER NULL NULL
0965 NULL ADCON 0078 NULL
0039 0010 NORTH BANK 1045 0106
0998 NULL MONEX BANK NULL 0010
0034 0010 NAT BANK 0939 0098
0330 0010 BARTHER M NULL 0095
0053 0010 BIDDY MULIGAN 0941 0106
0448 0013 BMW CORP. 0900 NULL
1044 0399 SAT. DISHES 1240 NULL
0006 NULL Lauren Moss NULL NULL
0013 NULL Josh Sanz NULL NULL
0596 NULL MGMT. CENTER 1908 0930
1038 NULL TIM KRAFT NULL NULL
0107 NULL MIND SHARE 0208 NULL
0900 NULL ROGER SMITH NULL NULL

Table B

Id Org_Number Account_Number

0006 NULL E5
0010 NULL E9
0011 NULL E10
0013 NULL E12
0034 BNM840515VB1 400160014
0053 BBA830831LJ2 400160016
0488 8888888020
0107 MSM981208FQ5 200180012
1038 NULL 8888888197
1037 ALM84030811A 9999999100
1038 NULL 8888888197
1042 NULL 8888888209
1043 NULL 8888888203
1044 CSA0702081Z2 400130154
1045 NULL 8888888199
1046 NULL 8888888201
1052 NULL NULL
1053 NULL 8888888212
1203 MWX0212129Q7 NULL
1204 GGP07100938A NULL

Output result should look like this:

Name Salesrep Contact Agency Org_Number Account_Number

Sales Team 1 NULL NULL NULL NULL E9
ABBOTT LABS Sales Team 1 TIM KRAFT MIND SHARE ALM84030811A 9999999100
BMW CORP. Josh Sanz ROGER SMITH NULL NULL 8888888020

And so on.

The idea is to sort of paste both tables side to side, having as the paste condition the coincidence of Id on both of them (A.Id = B.Id), in such a way that instead of having the Ids of the sales representatives, agencies and contacts, I get their names, which are mixed up along with the customer names on table A. This way you get the customer, your sales representative (if anyone specific) in charge of his account, his account and organization number (if they have one), the agency that brought him to you (if an agency brought that customer), and the person (if anyone specific) you should get in touch with for anything that has to do with this customer.

May today be good, and tomorrow better

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:47:55

see this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109492&SearchTerms=hierarchy

NiceSituation
Starting Member

22 Posts

Posted - 2008-10-27 : 14:55:39

Thanks, my problem is solved now.

May today be good, and tomorrow better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources