Joining Tables..

Hai all,
I am having three table and i need to join them...
First table : client [key field : clientid]
Second Table : Address [key field : addressid]
Third Table : contact [key field : forid]
the problem is having the third table like this :
ForId ContactTypeId ContactNo
-- -- --
ABC Phone 123
ABC Email abc@.abc.com
ABC Fax 00123456
XYZ Phone 123
XYZ Email xyz@.xyz.com
XYZ Fax 00123456
on joining i need the result should like
Clientid Address Phone Email Fax
-- -- -- -- --
123 asdcvb 123 abc@.abc.com 00123123
576 sdfsds 123 xyz@.xyz.com 00123456
Can anyone provide me the query plz?
Looking forward for the reply...
Thanx in advance..hi
send us the complete DDL and referencing key, so that we can give u an
accurate solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||>> Third Table : contact [key field : forid]
It is a huge problem so rather than masking the flaw with a complicated
query, restructuring the schema would be a better solution. Based on your
narrative, here is how:
CREATE TABLE new_tbl (
customer_id CHAR(3) NOT NULL PRIMARY KEY,
phone_nbr CHAR(10) NOT NULL,
email CHAR(40) NOT NULL
CHECK ( CHARINDEX( '@.', email ) > 1 )
fax CHAR(10) NOT NULL );
Now do:
INSERT new_tbl ( customer_id, phone_nbr, email, fax )
SELECT ForId,
MAX( CASE ContactTypeId WHEN 'Phone' THEN ContactNo
END ) AS "Phone",
MAX( CASE ContactTypeId WHEN 'Email' THEN ContactNo
END ) AS "Email",
MAX( CASE ContactTypeId WHEN 'Fax' THEN ContactNo
END ) AS "Fax"
FROM tbl
GROUP BY ForId ;
Once this is done sucessfully, dump the ill-designed table:
DROP TABLE contact ;
Once you have this new schema, your query should be as simple as having a
join. If due to some reason the table cannot be changed/deleted, then
consider using the SELECT portion in the above INSERT statement for warping
a short term kludge.
Anith