June 23, 2008 at 12:38 pm
I have four tables
customer(pk_Customerid,name,address,fk_phonetypeid)
customerphone(pk_customerphoneid,fk_customerid,fk_phoneid)
phone(pk_phoneid,fk_phonetypeid,phone)
phonetype(pk_phonetypeid)
phonetypeid : 1,2,3
where 1 represents mobile,2 represent landline and 3 represents others or fax.
In base table phone - phone,landline,fax are stored in the single column ie; phone and are identified by phonetypeid column, but for display i want those numbers to be in different columns.
Now i want to write a stored procedure which retrieve the customer details like:
customerid name mobile landline others or fax
----------------------------------------------------------
based on input parameter @customerid
Help Me!!!
June 23, 2008 at 2:18 pm
First of all, you have a mistake in your design in the first table, because as the relation between the phone and the customer tables are many to many, then the primary key for each table is put the third table (as you did), but there's no need to use the last column in the first table(fk_phonetypeid).
About the query suppose we have the following values:
PhoneTypes:
1 Mobile
2 Land
3 Fax or Other
Phones
1 - 1 - 111111
2 - 2 - 222222
3 - 3 - 333333
Customers:
1 - Feras - KSA
2 - Wael - Qatar
CustomerPhones (id, customerid, phoneid)
1 - 1(Feras) - 1(111111)
2 - 1(Feras) - 2(222222)
3 - 1(Feras) - 3(333333)
if we made the following query:
SELECT C.Name, P.Phone, PT.phoneType
FROM Customer C, Phone P,
customerphone CP, dbo.phonetype PT
WHERE C.pk_Customerid = CP.fk_customerid
AND P.pk_phoneid = CP.fk_phoneid
AND P.fk_phonetypeid = pk_phonetypeid
We'll get the following result:
Feras 111111 Mobile
Feras 222222 Land
Feras 333333 Fax Or Other
So what we need now is to convert the rows into columns, and we'll do this simple like this:
WITH #CustomersPhones AS
(
SELECT C.pk_Customerid As ID, C.Name, P.Phone, PT.phoneType, PT.pk_phonetypeid as TypeId
FROM Customer C, Phone P,
customerphone CP, dbo.phonetype PT
WHERE C.pk_Customerid = CP.fk_customerid
AND P.pk_phoneid = CP.fk_phoneid
AND P.fk_phonetypeid = pk_phonetypeid
)
SELECT CP1.Name, CP1.Phone as Mobile, CP2.Phone as Land, CP3.Phone as 'Fax or Other'
FROM #CustomersPhones CP1, #CustomersPhones CP2, #CustomersPhones CP3
WHERE CP1.Id = CP2.Id
AND CP1.Id = CP3.Id
AND CP1.TypeId = 1
AND CP2.TypeId = 2
AND CP3.TypeId = 3
You'll have the output like this:
Feras 111111 222222 333333
of course, you can limit the records with another condition in the WHERE (Id = @CustomerId)
I hope this is what you're looking for.
June 23, 2008 at 2:40 pm
Sorry forgot to mention currently i am working on sql server 2000 database.
You are right it will work.
can you suggest me something in sql server 2000. I have two parameters
@id int,@xyz varchar(9). by default they are null
June 23, 2008 at 2:44 pm
Use views instead of CTE, and you'll get the same result.
sorrowfully, currently i don't have SQL Server 2000 installed on my PC, so i can't test it.
Test it and tell me the results.
June 23, 2008 at 3:32 pm
Based on the schema fragments you've posted, I'm seeing the following situations:
- Different phone ids may represent the same phone number
- Customers may share phone numbers
- A customer may have 0 - infinite phone numbers of any type
Although it's not stated, I'm guessing the the relationship from Customer to PhoneType is the customer's preferred method of contact. That makes sense.
Given that, what do you want the result set to look like? What should it look like if customer A has 10 landline phone numbers?
If it's truly a requirement that a customer have at most zero-or-one mobile, zero-or-one landline and zero-or-one fax/other, then consider a schema like this:
CREATE TABLE Phone
(
PhoneId INT IDENTITY PRIMARY KEY,
Phone VARCHAR(9) UNIQUE
)
CREATE TABLE Customer
(
CustomerId INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(30) NOT NULL UNIQUE,
MobilePhoneId INT NULL REFERENCES Phone(PhoneId),
LandlinePhoneId INT NULL REFERENCES Phone(PhoneId),
FaxPhoneId INT NULL REFERENCES Phone(PhoneId),
)
This schema allows customers to share phone numbers and allows updating a phone number without a cascading update. I would also add an instead of delete trigger on Phone that implements the ON DELETE SET NULL functionality (it can't be added to all three foreign keys because it creates multiple paths).
I don't remember if "instead of" triggers exist in SQL 2000. If they don't, then remove the foreign keys and implement all the referential integrity in triggers the old-fashioned way.
June 23, 2008 at 4:47 pm
If you can change the schema as Stephanie Giovannini said, do that, because it's really a wonderfull schema design.
June 24, 2008 at 9:26 am
Thank You!
June 24, 2008 at 4:13 pm
Stephanie Giovannini (6/23/2008)
Based on the schema fragments you've posted, I'm seeing the following situations:- Different phone ids may represent the same phone number
- Customers may share phone numbers
- A customer may have 0 - infinite phone numbers of any type
Although it's not stated, I'm guessing the the relationship from Customer to PhoneType is the customer's preferred method of contact. That makes sense.
Given that, what do you want the result set to look like? What should it look like if customer A has 10 landline phone numbers?
If it's truly a requirement that a customer have at most zero-or-one mobile, zero-or-one landline and zero-or-one fax/other, then consider a schema like this:
CREATE TABLE Phone(
PhoneId INT IDENTITY PRIMARY KEY,
Phone VARCHAR(9) UNIQUE
)
CREATE TABLE Customer
(
CustomerId INT IDENTITY PRIMARY KEY,
CustomerName VARCHAR(30) NOT NULL UNIQUE,
MobilePhoneId INT NULL REFERENCES Phone(PhoneId),
LandlinePhoneId INT NULL REFERENCES Phone(PhoneId),
FaxPhoneId INT NULL REFERENCES Phone(PhoneId),
)
This schema allows customers to share phone numbers and allows updating a phone number without a cascading update. I would also add an instead of delete trigger on Phone that implements the ON DELETE SET NULL functionality (it can't be added to all three foreign keys because it creates multiple paths).
I don't remember if "instead of" triggers exist in SQL 2000. If they don't, then remove the foreign keys and implement all the referential integrity in triggers the old-fashioned way.
I'm going to recommend not doing it that way... what if the customer ends up with a 2nd land line or a 2nd fax or??? The Customer table should not even be aware of Telephones... it should (must) be the other way around... each telephone number must be aware of which customer owns it. And, it should be self aware of what type of phone it is...
Here's the simplified schema for that... you can go further out and make a snowflake by adding a definition table for phone type, address type, etc.
CREATE TABLE Phone
(
PhoneID INT IDENTITY PRIMARY KEY,
PhoneNumber VARCHAR(20), --Handles international country/city codes
CustomerID INT REFERENCES Customer(CustomerID),
PhoneTypeID INT REFERENCES PhoneType(PhoneTypeID)
)
CREATE TABLE Address
(
AddressID INT IDENTITY PRIMARY KEY,
Street1 VARCHAR(40) NOT NULL,
Street2 VARCHAR(40),
Street3 VARCHAR(40),
City VARCHAR(40) NOT NULL,
StateCode CHAR(2) NOT NULL REFERENCES State(StateCode)
Zip VARCHAR(9) NOT NULL,
CustomerID INT REFERENCES Customer(CustomerID),
AddressTypeID INT REFERENCES AddressType(AddressTypeID)
)
CREATE TABLE Customer
(
CustomerID INT IDENTITY PRIMARY KEY,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
MiddleInitial CHAR(1) NULL
)
ZipCode could reference a zip code table and also have a constraint to allow only 5 or 9 digits, etc, etc.
Address table would allow for multiple addresses including shipping addresses, physical addresses, mailing addresses (might be differenct than physical), etc, etc.
You could have as many phones and addresses as you want for a customer without adding columns to the customer table. If you want to see them together to get the nice warm fuzzies, then use a view.
You could go even a step further... customer could have multiple names or contacts...
--Jeff Moden
June 25, 2008 at 1:00 am
Kiran.. if you don't want to re-design your database, this query should work (I hope):
SELECT C.CustomerID,
C.Name,
P1.Phone as 'Mobile',
P2.Phone as 'LandLine',
P3.Phone as 'Fax or Other'
FROM Customer C
LEFT OUTER JOIN CustomerPhone CP
ON CP.CustomerID = C.CustomerID
LEFT OUTER JOIN (SELECT PhoneID, Phone FROM Phone WHERE PhoneTypeID = 1) P1
ON P.PhoneID = CP.PhoneID
LEFT OUTER JOIN (SELECT PhoneID, Phone FROM Phone WHERE PhoneTypeID = 2) P2
ON P.PhoneID = CP.PhoneID
LEFT OUTER JOIN (SELECT PhoneID, Phone FROM Phone WHERE PhoneTypeID = 3) P3
ON P.PhoneID = CP.PhoneID
WHERE C.CustomerID = @CustomerID
Note if a customer has more than one mobile, or fax, or other. then you'll get more than one row for that customer with your current design.
Note also, I use left outer joins to cater for the fact that a customer may only have one of the phones, if any. Either way a row should be returned for the customer.. with the missing phones set to NULL (you can use IsNull around the p1/p2/p3 phone columns to use a value to replace NULL if you wish).
June 25, 2008 at 9:09 am
Jeff, I like your schema better. Thanks for clarifying how phone numbers and addresses are ideally stored.
June 25, 2008 at 10:10 am
Janine and Jeff thank you.
At present i do not want to change my schema. Jenine hope your code will helped me in out in finding solution. This problem making me
:crazy:
June 25, 2008 at 5:16 pm
Stephanie Giovannini (6/25/2008)
Jeff, I like your schema better. Thanks for clarifying how phone numbers and addresses are ideally stored.
Heh... not sure it's the "ideal" way to do it, but I sure do appreciate the feedback. Thanks, Stephanie.
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply