Problem Returning Unique Companies

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)

Problem Returning Unique Companies

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-13 : 11:31:31

Can anyone please offer some advice/assistance with a SQL Query.

I have a view as follows:

SELECT DISTINCT
TOP 100 PERCENT
ISNULL(dbo.teamleadgen_contact.title, '') + ' ' +
ISNULL(dbo.teamleadgen_contact.first_name, '')
+ ' ' + ISNULL(dbo.teamleadgen_contact.last_name,
'') AS ContactName, dbo.aspnet_Users.UserName AS [Called By],
dbo.teamleadgen_company.name AS Company,
dbo.teamleadgen_calls.CallDate
FROM dbo.teamleadgen_calls INNER JOIN
dbo.aspnet_Users ON dbo.teamleadgen_calls.CalledBy
= dbo.aspnet_Users.UserId INNER JOIN
dbo.teamleadgen_company ON
dbo.teamleadgen_calls.CompanyID = dbo.teamleadgen_company.id INNER JOIN
dbo.teamleadgen_contact ON
dbo.teamleadgen_calls.ContactID = dbo.teamleadgen_contact.id
GROUP BY dbo.teamleadgen_company.name, dbo.teamleadgen_calls.CallDate,
dbo.aspnet_Users.UserName, ISNULL(dbo.teamleadgen_contact.title, '')
+ ' ' + ISNULL(dbo.teamleadgen_contact.first_name,
'') + ' ' + ISNULL(dbo.teamleadgen_contact.last_name, '')
ORDER BY dbo.teamleadgen_calls.CallDate

I need the query to return only unique companies distinguished by
dbo.teamleadgen_company.id which I presumed the GroupBy function would
return but for some reason I am getting dupes. Any ideas ?

www.creativenrg.co.uk

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-13 : 11:38:49

might not be what you want but:

SELECT
dbo.teamleadgen_company.id ,
max(ISNULL(dbo.teamleadgen_contact.title, '') + ' ' +
ISNULL(dbo.teamleadgen_contact.first_name, '')
+ ' ' + ISNULL(dbo.teamleadgen_contact.last_name,
'')) AS ContactName, max(dbo.aspnet_Users.UserName) AS [Called By],
max(dbo.teamleadgen_company.name) AS Company,
max(dbo.teamleadgen_calls.CallDate) as CallDate
FROM dbo.teamleadgen_calls INNER JOIN
dbo.aspnet_Users ON dbo.teamleadgen_calls.CalledBy
= dbo.aspnet_Users.UserId INNER JOIN
dbo.teamleadgen_company ON
dbo.teamleadgen_calls.CompanyID = dbo.teamleadgen_company.id INNER JOIN
dbo.teamleadgen_contact ON
dbo.teamleadgen_calls.ContactID = dbo.teamleadgen_contact.id
GROUP BY dbo.teamleadgen_company.id
ORDER BY CallDate

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-13 : 11:56:24

Just for my own personal education could I just confirm what you have changed and what it does. From what I can see of the amended query you have added MAX() around my select items - what exactly does that do ?

www.creativenrg.co.uk

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-13 : 12:40:27

I've taken all the returned columns out of the group by clause and just put in dbo.teamleadgen_company.id so that you get unique dbo.teamleadgen_company.id's. That means you have to select the values from the group to go with that row - I've put in max as you didn't give any info as to what you wanted.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources