should be a simple query

[hughespa]

I have a table
Customer Id, Contact Type, Contact entry
the data is like this
cust 1 Phone 012345
cust 1 fax 54321
cust1 Email Someone@somewhare
Cust 2 Phone 23456
cust 2 fax 65432
cust 3 phone 34567
cust 3 Email cust@site

I want to create a view from the above data with headings as custId,phone,fax, email
but as the data does not exist for all customers it only returns data for those who have all fields filled

any ideas

 

[tlbroadbent]

Do you have one table or three tables? If there are multiple tables, are you joining the tables in a query? If so, you may need to use OUTER JOINs rather than INNER JOINs.

If there is only one table, you should see all rows unless the query has slection criteria in a WHERE clause.

Please post the query you use. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sunila7]

Hi,

This is one solution to it....

Select t.* from (select distinct ContactType from customer) d
Left Outer join Customer t on d.ContactType=t.ContactType
order by t.CustId


Hope it helps...

Sunil

 

[tlbroadbent]

Sunil,

Why would you need a query like that? The following returns the same result set.

Select *
From Customer
order by CustId
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[sunila7]

Hi Terry,

Sorry,I think i had gone nuts.....(-:

Sunil

 

[hughespa]

There is a single source table containing all the data
the customer Id may have up to 3 separate entries/records one for each contact type (phone/fax or email)
The use of the distinct only returns the first instance of a record for that customer
The result needs to be a cross tab of the results where there is only one customer id entry with subsequent fields for the contact type if they exist if they do not it is to put a null value in that field

 

[hughespa]

The Sql I am attempting to use is

SELECT
Contact.Cntct_id,
Contact.Cntct_Title + ' ' + Contact.Cntct_First_Name + ' ' + Contact.Cntct_Last_Name
AS ContactName ,
(select ContactMethod.Cm_Entry
FROM ContactMethodType (NOLOCK) INNER JOIN
ContactMethod (NOLOCK) ON
ContactMethodType.CMT_Id = ContactMethod.CM_CMT_Id INNER
JOIN
ContactMethodLink (NOLOCK) ON
ContactMethod.Cm_Id = ContactMethodLink.CML_CM_Id INNER JOIN
Contact (NOLOCK) ON
ContactMethodLink.CML_Cntct_Id = Contact.Cntct_id INNER JOIN
Company (NOLOCK) ON Contact.Cntct_Cmp_Id = Company.Cmp_Id
Where CmT_id=1) AS Fax,
(select ContactMethod.Cm_Entry
FROM ContactMethodType (NOLOCK) INNER JOIN
ContactMethod (NOLOCK) ON
ContactMethodType.CMT_Id = ContactMethod.CM_CMT_Id INNER
JOIN
ContactMethodLink (NOLOCK) ON
ContactMethod.Cm_Id = ContactMethodLink.CML_CM_Id INNER JOIN
Contact (NOLOCK) ON
ContactMethodLink.CML_Cntct_Id = Contact.Cntct_id INNER JOIN
Company (NOLOCK) ON Contact.Cntct_Cmp_Id = Company.Cmp_Id
Where CmT_id=3) AS EMail

FROM ContactMethodType (NOLOCK) INNER JOIN
ContactMethod (NOLOCK) ON
ContactMethodType.CMT_Id = ContactMethod.CM_CMT_Id INNER
JOIN
ContactMethodLink (NOLOCK) ON
ContactMethod.Cm_Id = ContactMethodLink.CML_CM_Id INNER JOIN
Contact (NOLOCK) ON
ContactMethodLink.CML_Cntct_Id = Contact.Cntct_id INNER JOIN
Company (NOLOCK) ON Contact.Cntct_Cmp_Id = Company.Cmp_Id

this gives errors if the customer has more than one fax/ email entered or none(does not exist)

 

[tlbroadbent]

I'm very confused. You said, "There is a single source table containing all the data" but your query uses several tables.

If you have one table as described in your firsat post, you can create a CROSSTAB using the CASE function in T-SQL.

Select
Customer_Id,
Phone = Max(CASE WHEN Contact_Type = 'phone' Then Contact_entry Else Null End),
Fax = Max(CASE WHEN Contact_Type = 'fax' Then Contact_entry Else Null End),
Emial = Max(CASE WHEN Contact_Type = 'email ' Then Contact_entry Else Null End),
From Contact_Tbl
Group By Customer_ID Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[hughespa]

Sorry The Table is originally created from several tables
But thanks to your bit I have made some progress
using the following

SELECT dbo.Contact.Cntct_id,
Fax = Max(CASE WHEN dbo.ContactMethodType.CMT_Id = 1 Then dbo.ContactMethod.Cm_Entry Else Null End),
Email = Max(CASE WHEN dbo.ContactMethodType.CMT_Id = 3 Then dbo.ContactMethod.Cm_Entry Else Null End)
FROM dbo.ContactMethodType, dbo.ContactMethod, dbo.ContactMethodLink, dbo.Contact, dbo.Company
WHERE (dbo.Contact.Cntct_id = dbo.Company.Cmp_Id AND
dbo.ContactMethodLink.CML_Cntct_Id = dbo.Contact.Cntct_id AND
dbo.ContactMethod.Cm_Id = dbo.ContactMethodLink.CML_CM_Id AND
dbo.ContactMethodType.CMT_Id = dbo.ContactMethod.CM_CMT_Id)
GROUP BY dbo.Contact.Cntct_id,dbo.ContactMethodType.CMT_Id,
dbo.ContactMethod.Cm_Entry

now produces a result set of

id Fax Email
1 123 Null
1 Null s@Site
2 Null Null
2 Null x@ site

when I want

Id Fax Email
1 123 s@site
2 Null x@site

 

[tlbroadbent]

You are gouping by all the columns. You must only group by Cntct_id.

GROUP BY dbo.Contact.Cntct_id Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[hughespa]

Thanks Terry for all your help it is now doing what I want It was one of those times when I was blind to the obvious
Thanks again

Paul