Question of beginer

papa80

Hi every body i need to know how to do one think.So i have to table one is Employer other is customerEmployer.Employer have idEmployer,name.CustomerEmployer have idemplpoyer and idcustomer. SO my question is please give me query who give it to me employer.name with max customet how to do that???:(( N.Nikolov best regards

when i want to read something good just seat and type it

Colin Angus Mackay

Your tables are:

Employer

IdEmployer
Name

CustomerEmployer

IdEmployer
IdCustomer

This describes two thirds of a many-to-many join. The missing information is likely to be

Customer

IdCustomer
{other columns}

papa80 wrote:

SO my question is please give me query who give it to me employer.name with max customet how to do that???

What do you mean by max customer? What is a max customer? What defines the customer as being the max? If you mean the MAX(IdCustomer) then:

SELECT Employer.Name
FROM Employer
INNER JOIN CustomerEmployer ON CustomerEmployer.IdEmployer = Employer.IdEmployer
WHERE IdCustomer = (SELECT MAX(IdCustomer) FROM CustomerEmployer)

* Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

papa80

is my bad.Ok i need to show employer.name with max number of customeres do you know how will look now that query???

when i want to read something good just seat and type it

Colin Angus Mackay

SELECT Employer.Name, CustomerCount.NumCustomers
FROM Employer
INNER JOIN (SELECT IdEmployer, COUNT(*) AS NumCustomers
FROM CustomerEmployer) AS CustomerCount
ON CustomerCount.IdEmployer = Employer.IdEmployer

* Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

papa80

that is give me error Msg 8120, Level 16, State 1, Line 25 Column NumCustomers is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. how to solv it??

when i want to read something good just seat and type it

i j russell

Colin Angus Mackay wrote:

SELECT Employer.Name, CustomerCount.NumCustomersFROM EmployerINNER JOIN (SELECT IdEmployer, COUNT(*) AS NumCustomers FROM CustomerEmployer) AS CustomerCountON CustomerCount.IdEmployer = Employer.IdEmployer

You need to add ORDER BY CustomerCount.NumCustomers DESC to give you the maximum first. To get only the maximum, use SELECT TOP 1 Employer.Name, etc. SELECT TOP 1 Employer.[Name], CustomerCount.NumCustomers FROM Employer INNER JOIN (SELECT IdEmployer, COUNT(*) AS NumCustomers FROM CustomerEmployer GROUP BY IdEmployer) AS CustomerCount ON CustomerCount.IdEmployer = Employer.IdEmployer ORDER BY CustomerCount.NumCustomers DESC Ian -- modified at 9:26 Thursday 17th August, 2006

papa80

Thank you very much.That is what i need.This code is great.:-D:)

when i want to read something good just seat and type it