duplicate rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

duplicate rows

Author

Topic

pierznj
Starting Member

8 Posts

Posted - 2007-05-16 : 00:51:42

Hi. I'm a SQL Server newbie, very experienced with Access, developing an ASP.NET database editor web app. I query the database with a statement more or less in the following form:

SELECT organisation.OrgID, organisation.Name, organisation.whatever FROM services INNER JOIN servicegrouping ON services.serviceID=servicegrouping.serviceID INNER JOIN organisations ON servicegrouping.OrgID = organisations.OrgID WHERE services.service=x OR services.service=y

In other words, I have a database of organisations. The services offered by the organisations are in a separate table, and I only want to return organisations that offer services X or Y.

Okay, now if I did this in Access, this query would return just one record for each organisation that meets the condition, unless I was to include a field from the services table in the SELECT clause, in which case of course I would get one record for each organisation and unique service offered.

But in MS SQL, the query returns duplicate rows if there is more than service offered by the organisation that meets the WHERE condition (=x or =y). Why is this and what do I need to do to my SQL statement to ensure I only get unique rows?

pierznj
Starting Member

8 Posts

Posted - 2007-05-16 : 01:14:19

I might add that I tried using SELECT DISTINCT, but SQL Server spat it out: "The text, ntext, or image data type cannot be selected as DISTINCT". OK, so one of the fields is a "text" field. But surely I can still do this somehow?

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-16 : 03:59:27

SELECT
organisation.OrgID, organisation.Name, organisation.whatever , COUNT(1)
FROM services
INNER JOIN
servicegrouping
ON services.serviceID=servicegrouping.serviceID
INNER JOIN organisations
ON servicegrouping.OrgID = organisations.OrgID
WHERE services.service=x OR services.service=y
GROUP BY organisation.OrgID, organisation.Name, organisation.whatever

Necessity is the mother of all inventions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources