table top (1) with Inner join - 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 2008 Forums

Transact-SQL (2008)

table top (1) with Inner join

Author

Topic

eyalan
Starting Member

5 Posts

Posted - 2012-02-27 : 16:42:20

Hi,
I would like to get only the first result [top (1)] of 'Products.Name':


SELECT     Products.Name, Categories.Name AS Expr1
FROM         Categories INNER JOIN
                      Products ON Categories.Id = Products.Id

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-27 : 17:37:00

[code]SELECT p2.Name,
Categories.Name AS Expr1
FROM Categories
INNER JOIN (SELECT TOP (1) p1.Id, p1.Name from Products p1) AS p2
ON Categories.Id = p2.Id [/code]If Categories to Products is a one to many relationship then, may be even this would be sufficient?[code]SELECT TOP (1) Products.Name,
Categories.Name AS Expr1
FROM Categories
INNER JOIN Products
ON Categories.Id = Products.Id [/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-27 : 19:32:34

Do you want to use an explicit ORDER BY clause to define which record is the "top one" or are you just trying to limit the SELECT to a single record but don't care which one?

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 04:59:15

or if you want the top 1 per Categories.Name then


SELECT
    c.[Name] AS [CategoryName]
    ,tp.[ProductName] AS [ProductName]
FROM
    Categories AS c
    CROSS APPLY ( 
        SELECT TOP 1 p.[Name] AS [ProductName]
        FROM Products AS p
        WHERE p.[ID] = Categories.[ID]
        ORDER BY <orderingColumnHere>
        )
        AS tp

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

eyalan
Starting Member

5 Posts

Posted - 2012-02-28 : 05:23:56

Hi,
Thank you very much for trying to help!

I now understand better what I need. I will try to explain:

lets say I have table of Products:

Products:


Pid | Pname
-----------
1     name1
2     name2

And I have table of Product Sizes:

ProductSizes:


Pid | PsizeId | PsizeName | Pprice
-----------------------------------
1      1         Regular     50
1      2         Medium      65
2      1         Simple      30
2      1         Large       42

What I need is to select with inner join On Pid but only the first row
from ProductSizes.

The results should be:


Pid | Pname | PsizeId | PsizeName | Pprice
--------------------------------------------
1     name1     1         Regular     50
2     name2     1         Simple      30

Many Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 05:28:44

is this homework?


SELECT
    p.[Pid]
    , p.[Pname]
    , ps.[PsizeID]
    , ps.[PsizeName]
    , ps.[Pprice]
FROM
    Products AS p
    JOIN ProductSizes AS ps ON
        ps.[Pid] = p.[Pid]
WHERE
    ps.[PsizeName] = 'Regular'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

eyalan
Starting Member

5 Posts

Posted - 2012-02-28 : 05:35:37

I cannot filter by string it not always "Regular".
Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 05:39:38

well there is no other way to isolate 'the first'

The first row per [Pid] on ProductSizes based on what criteria? Is it the first by price?

If so then try the CROSS APPLY method.



SELECT
    p.[Pid]
    , p.[Pname]
    , tps.[PsizeID]
    , tps.[PsizeName]
    , tps.[Pprice]
FROM
     Products AS p
    CROSS APPLY ( 
        SELECT TOP 1
            ps.[PsizeID]
            , ps.[PsizeName]
            , ps.[Pprice]
        FROM ProductSizes AS ps
        WHERE ps.[Pid] = p.[Pid]
        ORDER BY ps.<orderingColumnHere>
        )
        AS tps

I've highlighted the bit you need to do in red

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

eyalan
Starting Member

5 Posts

Posted - 2012-02-28 : 05:56:07

Charlie, Thank you very much.

This is new to me. So I need further help with your code.
I have more INNER JOIN on 'Product' Table shall I use CROSS as well?

here is my complete query:


SELECT     Products.Name, ProductOptions.Name AS Expr6, CitiesInLanguages.CityName, CategoriesInLanguages.CategoryName, ProductFiles.FilePath, 
                      UsersInfo.City AS ShopCity, ShopsInCities.DeliveryPriceUser, ProductOptions.PriceCustomer
FROM         Cities INNER JOIN
                      ShopsInCities ON Cities.Id = ShopsInCities.CityId INNER JOIN
                      CitiesInLanguages ON Cities.Id = CitiesInLanguages.CityId INNER JOIN
                      UsersInfo ON ShopsInCities.ShopId = UsersInfo.UserId INNER JOIN
                      ProductstInCategories INNER JOIN
                      Products INNER JOIN
                      ProductOptions ON Products.Id = ProductOptions.ProductId INNER JOIN
                      ProductsInProdutGroup ON Products.Id = ProductsInProdutGroup.ProductId INNER JOIN
                      ProductsGroups ON ProductsInProdutGroup.ProductGroupId = ProductsGroups.Id INNER JOIN
                      ShopsInProductsGroup ON ProductsGroups.Id = ShopsInProductsGroup.ProductGroupId ON ProductstInCategories.ProductId = Products.Id INNER JOIN
                      Categories ON ProductstInCategories.CategoryId = Categories.Id INNER JOIN
                      CategoriesInLanguages ON Categories.Id = CategoriesInLanguages.CategoryId INNER JOIN
                      ProductFiles ON Products.Id = ProductFiles.ProductId ON UsersInfo.UserId = ShopsInProductsGroup.ShopId
WHERE     (ShopsInCities.IsEnable = 1) AND (Categories.IsEnable = 1) AND (Cities.IsEnable = 1) AND (CategoriesInLanguages.CategoryName = @CategoryName) AND 
                      (CitiesInLanguages.CityName = @CityName) AND (ShopsInProductsGroup.IsEnable = 1) AND (ProductsGroups.IsEnable = 1) AND (ProductFiles.FileTypeId = 1) AND 
                      (ShopsInCities.IsEnable = 1) AND (ProductOptions.IsEnable = 1) AND (Products.IsEnable = 1)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 06:25:57

OK -- I think you are a little confused.

I take it that query was written with the graphical interface? It generally produces very unreadable code.

you haven't told us exactly what you are trying to do with the Products and ProductsInCategories tables.

Have you heard of aliases? they make the code a lot more readable.

I don't want to spend my time wading through your sql.

APPLY is a keyword that you are probably not familiar with. You can read about it here.
http://technet.microsoft.com/en-us/library/ms175156.aspx

But lets go back to basics.

What do you actually need from the tables in question? can you post full sample data and the expected results?

remember we don't know your environment.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

eyalan
Starting Member

5 Posts

Posted - 2012-02-28 : 07:10:28

I managed to do it with:

select [Id] from (
SELECT [Id] = MIN([Id]), [ProductId]
FROM [ProductOptions]
group by [ProductId]
) Z

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources