Alias - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Alias

Author

Topic

RichardChristie
Starting Member

2 Posts

Posted - 2007-04-11 : 06:08:08

I have a sql statement which joins a number of tables together, all of which are related to items:

SELECT Item.ID, Item.ItemLookupCode, Item.Description, Item.Price, 
Item.Quantity, Department.Name as DepartmentName, Category.Name as CategoryName, 
Supplier.SupplierName, Department.Code as DepartmentCode, Category.Code as 
CategoryCode, Item.BinLocation, Item.ExtendedDescription, Item.SubDescription1, Item.SubDescription2, 
Item.SubDescription3, Item.UnitOfMeasure, Item.BlockSalesReason, Item.Notes, 
Item.ItemType, 
Alias.Alias 
FROM Item 
LEFT JOIN Department ON Item.DepartmentID = Department.ID 
LEFT JOIN Category ON Item.CategoryID = Category.ID 
LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID 
LEFT JOIN Alias ON Item.ID = Alias.ItemID

If an item has more than one Alias that record is returned multiple times (how ever many Aliases there are).

I want to change the statement so that it returns only one record per item, regardless to the number of Aliases. I also want to be able to add a where clause to search on those Aliases where necessary.

Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-11 : 06:12:57

SELECT x.ID, x.ItemLookupCode, x.Description, x.Price, x.Quantity, x.DepartmentName, x.CategoryName,
x.SupplierName, x.DepartmentCode, x.CategoryCode, x.BinLocation, x.ExtendedDescription, x.SubDescription1, x.SubDescription2,
x.SubDescription3, x.UnitOfMeasure, x.BlockSalesReason, x.Notes, x.ItemType, x.Alias
FROM (
SELECT Item.ID, Item.ItemLookupCode, Item.Description, Item.Price,
Item.Quantity, Department.Name as DepartmentName, Category.Name as CategoryName,
Supplier.SupplierName, Department.Code as DepartmentCode, Category.Code as
CategoryCode, Item.BinLocation, Item.ExtendedDescription, Item.SubDescription1, Item.SubDescription2,
Item.SubDescription3, Item.UnitOfMeasure, Item.BlockSalesReason, Item.Notes,
Item.ItemType,
Alias.Alias, ROW_NUMBER() OVER (PARTITION BY Item.ID ORDER BY Item.Price DESC) AS RecID
FROM Item
LEFT JOIN Department ON Item.DepartmentID = Department.ID
LEFT JOIN Category ON Item.CategoryID = Category.ID
LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID
LEFT JOIN Alias ON Item.ID = Alias.ItemID WHERE Alias.Alias = 'xxxxxx'
) AS x where x.RecID = 1

Peter Larsson
Helsingborg, Sweden

RichardChristie
Starting Member

2 Posts

Posted - 2007-04-11 : 06:22:16

Wow, thanks Peter.

This works brilliantly. Thanks for the fast response too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources