Select statement with group by

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)

Select statement with group by

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2006-11-24 : 11:33:00

I'm fairly new to T-SQL and often encounter this error message.
Your advice would be most helpful
Thanks

/*
I have a select statement where an error message returns;

Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I understand this is because my grouped records contains more than 1 value. Is there a way to do this without me creating a temp table where I know the join will work?
*/
insert into MainTable
(PersonName, PersonID)
SELECT
PersonName, PersonID
FROM SmallerTable
INNER JOIN GroupedRecords ON SmallerTable.PersonID =
(SELECT GroupedRecords.PersonID
FROM GroupedRecords
GROUP BY GroupedRecords.PersonID)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-24 : 12:10:08

Either of these should do what you want - it looks like your data may be pretty badly designed but you don't give enough info for me to give you any more than a working version of this query.

insert into MainTable
(PersonName, PersonID)
SELECT
PersonName, PersonID
FROM SmallerTable
INNER JOIN (SELECT GroupedRecords.PersonID
FROM GroupedRecords
GROUP BY GroupedRecords.PersonID) GroupedRecords
ON SmallerTable.PersonID = GroupedRecords.PersonID

OR

insert into MainTable
(PersonName, PersonID)
SELECT
PersonName, PersonID
FROM SmallerTable
INNER JOIN GroupedRecords ON SmallerTable.PersonID = GroupedRecords.PersonID
GROUP BY GroupedRecords.PersonID, SmallerTable.PersonName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 13:53:02

Not to mention

insert           MainTable
                 (
                     PersonName,
                     PersonID
                 )
SELECT DISTINCT  st.PersonName,
                 gr.PersonID
FROM             SmallerTable st
INNER JOIN       GroupedRecords gr ON st.PersonID = gr.PersonID

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-24 : 14:15:57

quote:

Originally posted by Peso

Not to mention

insert           MainTable
                 (
                     PersonName,
                     PersonID
                 )
SELECT DISTINCT  st.PersonName,
                 gr.PersonID
FROM             SmallerTable st
INNER JOIN       GroupedRecords gr ON st.PersonID = gr.PersonID

Peter Larsson
Helsingborg, Sweden

I assumed that wouldn't work because there are duplicates in GroupedRecords (otherwise no need to be grouping in the OP subquery) - but we're both wildly guessing at what is really happening because the data is not given

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 14:23:16

Using DISTINCT or as you, GROUP BY for all columns, produces the same result

Peter Larsson
Helsingborg, Sweden

DLTaylor
Posting Yak Master

136 Posts

Posted - 2006-11-27 : 06:16:48

thank you all.
Got back to work and have picked up your replies.
The first reply snSQL - worked perfectly.
Thank again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 06:37:10

This seems to performa just a little faster

SELECT		st.PersonName,
		st.PersonID
FROM		@st st
WHERE		EXISTS (SELECT * FROM @gr gr WHERE gr.PersonID = st.PersonID)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources