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.
| Author |
Topic |
|
Matt2k9
Starting Member
10 Posts |
 Posted - 2008-09-05 : 10:41:20
|
| Hi, I have a subquery that will just not work. select product.productid, productname, datecreated,(select qualificationName from product_qualification whereproduct_qualification.qualificationid = product.qualificationid) as qualificationfrom productThe above query works fine, I am just pulling the qualification name related to the product by qualificationid from a lookup table.BUT when I use the exact same query design against a junction table I get the error "subquery returned more than 1 value, this is not permitted when the subquery follows, =, !=, < (etc) or when the subquery is used as an expression".The query that returns this error is:select product.productid, productname,(select locationid, price from product_availabilityJunction where product_availabilityJunction.productId = product.productid) as availabilityfrom productSo this query does not work even though it uses the same pattern as the previous query. The only reason I can think of is that in the 2nd query I'm trying to use a subquery on a junction table (with a combined primary key of productid + locationid).This is really annoying, any ideas why this is not working?I should also note that I cannot use joins for this due to some asp.net reasons. (urggh)Any ideas would be appreciated!Thanks,Matt |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-07 : 00:35:46
|
use join instead of subqueryselect p.productid, p.productname,paj.locationid, paj.pricefrom product pinner join product_availabilityJunction pajon paj.productId = p.productid |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-08 : 03:15:09
|
| << should also note that I cannot use joins for this due to some asp.net reasons. (urggh)>>What are the resaons?MadhivananFailing to plan is Planning to fail |
|
|
|
Matt2k9
Starting Member
10 Posts |
Posted - 2008-09-08 : 06:09:22
|
| Thanks, I think I am going to have to go down the join route afterall, this subquery stuff is just crazy.I am using Visual Studio to automatically write some of my data access layer for me, using sub queries it will create my methods for inserts, updates and deletes using optimized and connection safe code. Unfortunately if the queries you point the compiler at have joins, it won't work. Normally the thing to do is point it at queries using sub querys then change your sprocs to use joins afterwards. I have come up with a work-around though. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
Subquery not working on junction table?