Complex query and merge of three tables

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 2008 Forums

Transact-SQL (2008)

Complex query and merge of three tables

Author

Topic

panayiotis
Starting Member

16 Posts

Posted - 2010-09-28 : 05:04:30

Hello,

I have a database with a location table which looks like this:

LocationID int
ParentLocationID null
Name nvarchar
<<Other>>

A Unit table which looks like this
UnitID
LocationID
UnitTypeID
<<Other>>

A Unit Type table which looks like this
UnitTypeID
UnitName

I need to write a query which will return me based on top parent location the count of units and then group them per unit type.

So it should look like this:

London 10
UnitType A 8
UnitType B 2
Total 20

Bristol 5
UnitType A 3
UnitType B 2
Total 10

I am sure i have seen something similar in an ecomerce query.

Can you please advice.

Thanks

panayiotis
Starting Member

16 Posts

Posted - 2010-09-28 : 08:48:27

Found the example with the ecommerce query but still i can not do what i have described above.

http://www.sqlusa.com/bestpractices2005/subtotaltotalgrandtotal/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources