June 29, 2005 at 9:03 am
Hello all,
I have a query that returns a list of "projects" and phone numbers associated to them. My problem is that I am getting two listings for a project that has two phone numbers associated with it, 3 for 3, and so on. I am not concerned with which phone number gets returned, I just want one. I'm sure this one is simple, I am just missing it!
SELECT DISTINCT P.PROJECTNUM,
P.PO,
P.Region,
P.Customer,
P.CustomerContact,
P.Location,
P.LocationContact,
P.Project_Loc_Phone,
P.AUTHORIZATIONFORMSENT,
E.Area_Code,
E.ADDRESS_STRING,
P.Project_Loc_Contact1_ID
FROM Project_Info_vu P
LEFT JOIN ELECTRONIC_ADDRESS E ON P.Customer_Contact1_ID = E.CONTACT_ID
WHERE (P.PO Like 'dnr%'
OR P.PO Like 'wnp%'
OR P.PO Is Null
OR P.PO Like 'natc%'
OR P.AUTHORIZATIONFORMRCVD Is Null)
AND
(P.AcceptedBy Is Not Null AND P.DATEEND Is Null AND P.Status Is Null
AND P.Project_Deleted Is Null AND E.DESCRIPTION Like 'tele%')
Thanks in advance!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 29, 2005 at 9:26 am
You are getting what you asked for. If for example your project table contains in part:
Customer_Contact_ID Customer Location Region PO
1 IBM US NorthAmerica 2
2 Apple UK Europe 4
ELECTRONIC_ADDRESS
Customer_Contact_ID Area_Code ADDRESS_STRING,
1 613 123-1234
1 613 567-1718
2 203 44-56-5555
2 44 34-88-8933
2 44 55-565-955
You will receive as a result of your query 2 lines for IBM and 3 lines for Apple. You say you want one line. (why?) Well you must tell SQL Server which one. You could do this by grouping by Customer_Contact_ID. You still need to say which of rows corresponding to the non-grouped columns you want. This is done with functions like MAX or MIN.
for example:
SELECT MAX(P.PROJECTNUM),
MAX(P.PO),
MAX(P.Region),
MAX(P.Customer),
MAX(P.CustomerContact),
MAX(P.Location),
MAX(P.LocationContact),
MAX(P.Project_Loc_Phone),
MAX(P.AUTHORIZATIONFORMSENT),
MAX(E.Area_Code),
MAX(E.ADDRESS_STRING),
P.Project_Loc_Contact1_ID
FROM Project_Info_vu P
LEFT JOIN ELECTRONIC_ADDRESS E ON P.Customer_Contact1_ID = E.CONTACT_ID
WHERE (P.PO Like 'dnr%'
OR P.PO Like 'wnp%'
OR P.PO Is Null
OR P.PO Like 'natc%'
OR P.AUTHORIZATIONFORMRCVD Is Null)
AND
(P.AcceptedBy Is Not Null AND P.DATEEND Is Null AND P.Status Is Null
AND P.Project_Deleted Is Null AND E.DESCRIPTION Like 'tele%')
GROUP BY Customer_Contact1_ID
Somehow this just doesn't seem right, but then there does seem something odd about wanting to display one one phone number when there exist multiple.
Francis
June 29, 2005 at 9:41 am
Francis,
That worked for what I needed. Just for curiosity though, is there an easy way to get the multiple entries in one field?
So if ....
Customer_Contact_ID Customer Location Region PO
1 IBM US NorthAmerica 2
2 Apple UK Europe 4
ELECTRONIC_ADDRESS
Customer_Contact_ID Area_Code ADDRESS_STRING,
1 613 123-1234
1 613 567-1718
2 203 44-56-5555
2 44 34-88-8933
2 44 55-565-955
The result set would be...
Customer_Contact_ID Customer Phone
1 IBM 123-1234, 567-1718
2 APPLE 44-56-5555, 34-88-8933, 55-565-955
This is not an issue, I'm just curious how it's possible.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 29, 2005 at 10:53 am
I may have been too hasty here. Try something like: SELECT P.PROJECTNUM,
P.PO,
P.Region,
P.Customer,
P.CustomerContact,
P.Location,
P.LocationContact,
P.Project_Loc_Phone,
P.AUTHORIZATIONFORMSENT,
E.Area_Code,
E.ADDRESS_STRING,
P.Project_Loc_Contact1_ID
FROM Project_Info_vu P
LEFT JOIN ELECTRONIC_ADDRESS E ON P.Customer_Contact1_ID = E.CONTACT_ID
JOIN (SELECT MIN(ADDRESS_STRING) MIN_ADDRESS_STRING FROM Project_Info_vu GROUP BY Customer_Contact1_ID) Proj_inv
ON Proj_inv.MIN_ADDRESS_STRING = e.ADDRESS_STRING AND Proj_inv.CONTACT_ID =P.Customer_Contact1_ID
WHERE (P.PO Like 'dnr%'
OR P.PO Like 'wnp%'
OR P.PO Is Null
OR P.PO Like 'natc%'
OR P.AUTHORIZATIONFORMRCVD Is Null)
AND
(P.AcceptedBy Is Not Null AND P.DATEEND Is Null AND P.Status Is Null
AND P.Project_Deleted Is Null AND E.DESCRIPTION Like 'tele%')
See http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=195242&p=3 if you handle slogging through all the comments it may help.
As for your question about displaying all the phone numbers in one caloumn. This is related to pivoting a table; a good example of doing this is found at http://www.sqlservercentral.com/scripts/contributions/506.asp
Francis
June 29, 2005 at 10:54 am
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=186228
In this post you will find that.
Regards,
gova
June 29, 2005 at 12:10 pm
Thanks to ALL for your replies. In the end I went with the function, and it works great!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply