Question about subqueries - 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

Question about subqueries

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 17:18:02

I created two subqueries: One subquery is looking for the ComponentID for the product number 'to1234 and the second will find all the serial numbers connected to that componentid. Finally I want to use all the serial numbers I got (which is about 150) and use them to find all of the convertibles (ModelType). I thought that this query would work but I get this error: Subquery returned more than 1 value. I thought that you can use subqueries to return more than one value?

Select *
From Car
Where SerialNumber= (

Select bikepart.SerialNumber
From CarParts carpart
Where Carpart.ComponentID = (

Select component.ComponentID
From Component component
Where component.ProductNumber = 'to1234') )

Thanks for any help and advice you can give.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 17:29:04

try changing = to IN

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-18 : 17:31:02

Why not just do this?


    SELECT *
      FROM Car c
INNER JOIN CarParts cp
	ON c.SerialNumber = c.SerialNumber = cp.SerialNumber
INNER JOIN Component cm
	ON cp.ComponentID = cm.ComponentID
     WHERE cm.ProductNumber = 'to1234'

Kinda confused where this comes from though

bikepart.SerialNumber

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 17:32:52

or like this:


Select	c.*
From	Car c
JOIN	CarParts p
On	p.SerialNumber = c.SerialNumber
JOIN	Component cm
On	cm.ComponentID = p.ComponentID
Where	cm.ProductNumber = 'to1234'

Also, I assume BikePart is a typo and you meant carpart...?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 17:33:25

LOL Brett. Beat me by a few secs saying same thing again

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 19:28:02

Oh sorry about that. Is it better to do queries the way you did them? What is the difference when doing queries this way:Select c.*
From Car c
JOIN CarParts p
On p.SerialNumber = c.SerialNumber
JOIN Component cm
On cm.ComponentID = p.ComponentID
Where cm.ProductNumber = 'to1234'

Then This way:
Select *
From Car
Where SerialNumber in (

Select bikepart.SerialNumber
From CarParts carpart
Where Carpart.ComponentID = (

Select component.ComponentID
From Component component
Where component.ProductNumber = 'to1234') )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-18 : 19:30:17

The join approach is much more efficient than the approach you posted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 19:51:39

So when should subqueries be used?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-18 : 19:54:06

I try to avoid them if at all possible. A lot of times joins can be used instead. So it just depends on what data you need to have returned and what's the most efficient approach. Often times, there are many different ways you can write a query, so you need to compare the execution plans and execution times to decide which is best.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 21:12:26

Wow thank you so much for all the help. I was wondering Can do this in the select:

I was just trying to see if I can get the number of the models in that query and divide is by the number of race car modeltype in the car table.

Select Count(c.ModelType) / Select Count(ModelType) From Car Where ModelType = 'Race'
From Car c
JOIN CarParts p
On p.SerialNumber = c.SerialNumber
JOIN Component cm
On cm.ComponentID = p.ComponentID
Where cm.ProductNumber = 'to1234' And c.Modeltype = 'Race' And c.OrderDate between '20020101' and '20021231'

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 21:15:56

Would I have to create another From car c1 and use that in the second count()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources