Complex Queries Help Needed!!!!!!!

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

Complex Queries Help Needed!!!!!!!

Author

Topic

Dreaming_Demon
Starting Member

3 Posts

Posted - 2011-01-12 : 08:56:19

OK, so I'm studying for the Technical Specialist Exam for Microsoft SQL Server 2005, and I've having some problems with one of the tasks. Using the Adventureworks database I need to "Write a query that gives the hierarchy of the components that are needed to build a particular product. Using a CTE named parts, which will contain the AssemblyID, ComponentID, and ComponentName Columns. Use ProductID 800 in your solution".

I'm really not sure where to being with this. Firstly what information in the tables do I need to use to work out the components for the given product? The Task suggests using the Production BillOfMaterials, product and ProductInventory tables, i'm just not sure what columns and how. As for the CTE would Using UNION ALL the right way to go about it?

I'd Be Greatfull if anyone could point me in the right direction, Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 09:16:23

see the link

http://www.mssqltips.com/tip.asp?tip=1520

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dreaming_Demon
Starting Member

3 Posts

Posted - 2011-01-12 : 12:41:22

thank you. That rather helpful. So I've written

WITH Components AS
( SELECT BoM.ProductAssemblyID, BoM.ComponentID, pp.ProductID
FROM Production.BillOfMaterials BoM JOIN Production.Product pp ON BoM.ComponentID= ProductID
UNION ALL
SELECT ProductID
FROM Production.Product
WHERE ProductID = 800
)

GO

SELECT * FROM Components

Which I think would supply the needed list of components needed to make a particular product, I think. But the above code gives an error stating: Incorrect syntax near ')'.

Where am I going wrong?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-12 : 12:42:15

GO is a batch separator. Remove that and things should work.

Dreaming_Demon
Starting Member

3 Posts

Posted - 2011-01-13 : 16:12:01

Took, out the GO operator and it gave me a different error but I've managed to sort it. Cheers for the help, much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 05:48:54

i think the other error was because you had unequal number of columns on both sides of UNION ALL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources