Problems using Group By - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Problems using Group By

Author

Topic

bugbite
Starting Member

5 Posts

Posted - 2004-11-24 : 04:06:38

Hello all,

I am returning eleven columns from two tables. My tables and columns are:
EDITION - ISBN, Title, Sub_title, Publication_Date, No_of_pages, binding_code, short_blurb, long_blurb, reviews
PARTY - firstname, surname

The link between the EDITION and PARTY tables is through two tables, AGREEMENT and ROLE.

When I run my SQL statement it should return one row (when I add a where clause), but due to there being 2 agreements linked to this one title it is returning two rows instead as I need to go through AGREEMENT table to display the PARTY table details (I do not want to display any AGREEMENT details).

My SQL code is:
SELECT edition.ISBN, edition.title, edition.sub_title, party.first_name + N' ' + party.surname AS Name, edition.publication_date, edition.no_of_pages, edition.binding_code, edition.short_blurb, edition.long_blurb, edition.reviews
FROM edition INNER JOIN
agreement ON edition.edition_id = agreement.edition_id INNER JOIN
role ON agreement.role_id = role.role_id INNER JOIN
party ON role.party_id = party.party_id

I have tried to use GROUP BY which works fine if I display the following columns:
ISBN, title, sub_title, publication_date, no_of_pages, binding_code, firstname, surname

but if I try to also return the columns reviews, short_blurb and long_blurb I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'edition.short_blurb' is invalid in the select list because it is not contained in either an aggregate function or the group by clause

The field properties for these three columns are ntext whilst the rest are a mixture of nvarchar, int & char.

Any idea how I can get around this?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-24 : 07:37:10

dare I ask why you jave two agreements then?

you could always use TOP

ie


SELECT Top 1
	edition.ISBN, 
	edition.title, 
	edition.sub_title, 
	party.first_name + N' ' + party.surname AS Name, 
	edition.publication_date, 
	edition.no_of_pages, 
	edition.binding_code, 
	edition.short_blurb, 
	edition.long_blurb, 
	edition.reviews
FROM edition 
INNER JOIN agreement 
ON edition.edition_id = agreement.edition_id 
INNER JOIN role 
ON agreement.role_id = role.role_id 
INNER JOIN party 
ON role.party_id = party.party_id
--Where blah blah

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources