Subquery returned more than 1 value

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)

Subquery returned more than 1 value

Author

Topic

rx7lover86
Starting Member

5 Posts

Posted - 2004-09-08 : 11:01:14

Hopefuly someone can help me because I'm at a loss.

I have 2 tables, TimeRate and ipPeople.
I need to look up the most recent bill rate(tkrt03) for a timekeeper from the TimeRate table. This is the query I have to do that:

SELECT tkrt03
FROM timerate
WHERE (tkeffdate =
(SELECT MAX(tkeffdate)
FROM timerate
WHERE (tkinit = '0578'))) AND (tkinit = '0578')

Now this is only for the timekeepr number 0578. What I need it to do is to look up the timekeepers from the ipPeople table. So in place of the 0578 I would have a query like this:

SELECT tkid
FROM ipPepole
WHERE lupeopletypepkid = '3'

However this query could possibly return multiple values, in which case the first query will throw an error:
"subquery returned more than 1 value. This is not permitted when the subquery follows = ..."

How would I rewrite this query ? Any help would be appreciated. Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-08 : 11:07:58

would something like this work for you:

SELECT tkrt03
FROM timerate t1
inner join (SELECT timerateId, MAX(tkeffdate) as tkeffdate
FROM timerate group by timerateId
) t2 on (t1.timerateId = t2.timerateId) and (t1.tkeffdate = t2.tkeffdate)
WHERE (t1.tkinit = '0578')

and then u join the results with ipPepole table

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-08 : 11:08:12

Maybe this is what you want??

SELECT tkrt03
FROM timerate
WHERE
(tkeffdate = (SELECT MAX(tkeffdate) FROM timerate WHERE tkinit in (SELECT tkid FROM ipPepole WHERE lupeopletypepkid = '3'))
And tkinit in (SELECT tkid FROM ipPepole WHERE lupeopletypepkid = '3')

Corey

rx7lover86
Starting Member

5 Posts

Posted - 2004-09-08 : 11:25:19

Spirit -

What you had works for returning the most recent rate from the timerate table, however when i put a Select statment in place of the 0578 I get the same error again about Subquery returned more than 1 value.

I need to look up multiple values from one table, based upon multiple values from another table. In seperate database, with different collation no less, but that's besides the point. Is this even possible? I can't say I've ever run into anything like this before.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-08 : 11:32:39

well more info would be nice...

give us create table, insert into (sample data) statements and desired result. so we can try that on our machines.
now we're just guessing what might be...

have you tried coreys solution??

Go with the flow & have fun! Else fight the flow

rx7lover86
Starting Member

5 Posts

Posted - 2004-09-08 : 11:43:10

Yes I tried Corey's solution..., it does not return the desired results. I'm not quite sure what it's returning, but it's always the same 2 values.

I'm not sure what more info I have for you. I'm not creating or inserting data. All I'm doing is selecting it, once I can select the data I'll have no problem inserting it into the proper tables.

The timerate table is basically just a bunch of people with different bill rates, several per timekeeper so that's why I have to select the most recent.

The ipPeople table is a table that consists of the people who are the billers for this specific case.

So there could be 2 people working on a case that will be the billers. Lets say biller 0001 and 0002. We need to find the bill rate for both of them from the timerate table.
Timerate.tkinit = ipPeople.tkid

Unless there's a way to do this in multiple steps? Like first grab the timekeep numbers and then process them one at a time?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-08 : 12:00:56

will this help u? i hope i got table structure right:


declare @Timerate table (tkinit int, tkeffdate datetime)
insert @Timerate
select 1, '20040615' union all
select 1, '20040616' union all
select 1, '20040626' union all
select 2, '20040617' union all
select 2, '20040610' union all
select 3, '20040611' union all
select 3, '20040622' 

declare @ipPeople table (tkid int, name varchar(50))
insert @ipPeople
select 1, 'Bill' union all
select 2, 'Jake' union all
select 3, 'John'

select * from @Timerate
select * from @ipPeople

SELECT t1.*
FROM @Timerate t1
	inner join (SELECT tkinit, MAX(tkeffdate) as tkeffdate
			FROM @Timerate group by tkinit
		) t2 on (t1.tkinit = t2.tkinit) and (t1.tkeffdate = t2.tkeffdate)
	inner join @ipPeople t3 on t1.tkinit = t3.tkid
order by 1

Go with the flow & have fun! Else fight the flow

rx7lover86
Starting Member

5 Posts

Posted - 2004-09-08 : 13:28:22

Let me try to simplify things. Lets take the whole date part out.

Timerate (tkid varchar(8), billrate nvarchar(9))
ipPeople (tkid varchar(8), name varchar(50), type nvarchar(1))

is there anyway to get this query to work if the ipPeople table returns multiple values?

SELECT billrate
FROM timerate
WHERE tkid = (SELECT tkid
FROM ipPeople
WHERE type = '3')

I really do appreciate your help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-08 : 13:30:02

Not in the way you want.
you coud use in or exist...

SELECT billrate
FROM timerate
WHERE tkid in (SELECT tkid
FROM ipPeople
WHERE type = '3')

Go with the flow & have fun! Else fight the flow

rx7lover86
Starting Member

5 Posts

Posted - 2004-09-08 : 14:27:44

I believe I've made some progress, this works with my Test table, which is on the same server as the timerate table.

SELECT timerate.tkrt03 AS EXPR1, timerate.tkinit
FROM Test2 INNER JOIN
timerate ON Test2.TKID = timerate.tkinit
WHERE (timerate.tkeffdate =
(SELECT MAX(tkeffdate)
FROM timerate INNER JOIN
Test2 ON timerate.tkinit = Test2.TKID))

However This does not work on my actual live data. This one is using ipPeople, which is on a different server but shouldn't matter because they are linked.

SELECT t1.tkrt03, t1.tkinit
FROM ipPeople INNER JOIN
mbf420.son_db.dbo.timerate t1 ON ipPeople.TKID COLLATE database_default = t1.tkinit
WHERE (t1.tkeffdate =
(SELECT MAX(t1.tkeffdate) AS Expr1
FROM mbf420.son_db.dbo.timerate t2 INNER JOIN
ipPeople ON t2.tkinit = ipPeople.TKID COLLATE database_default)) AND (ipPeople.FolderID = N'MERCURY$MBF-FIRM$00000584')

With this I'm getting an error that says "An Aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources