CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eyal
Eyal

Reputation: 4763

Get value from one of the fields

I have a query that gives:

orp      CategoryId     Category        City      

999         1           bouquets        city-a       
999         2           arrengmnt       city-b       
999         4           chocolate       city-c

I want to add in each row the number of shops having the category:

orp     CategoryId      Category        City      NoShops

999         1           bouquets        city-a       10
999         2           arrengmnt       city-b       14
999         4           chocolate       city-c       3

I need to get the CategoryId (@CategoryId), So I was thinking about something like this (obviously is't not working):

SELECT  Categories.OrderInPage as orp, CategoriesInLanguages.CategoryName,  @CategoryId = Categories.Id     
                ,(
                    SELECT COUNT(Shops.Id) as NoShops
                        FROM  Cities INNER JOIN
                                      CitiesInLanguages ON Cities.Id = CitiesInLanguages.CityId INNER JOIN
                                      ShopsInCities ON Cities.Id = ShopsInCities.CityId INNER JOIN
                                      CategoriesInLanguages INNER JOIN
                                      Categories ON CategoriesInLanguages.CategoryId = Categories.Id INNER JOIN
                                      ProductstInCategories ON Categories.Id = ProductstInCategories.CategoryId 
                        WHERE      (Products.IsEnable = 1)  AND ( Categories.Id = @CategoryId)) AS Z  
                )
        FROM  Cities INNER JOIN
                          CitiesInLanguages ON Cities.Id = CitiesInLanguages.CityId INNER JOIN
                          ShopsInCities ON Cities.Id = ShopsInCities.CityId INNER JOIN
                          CategoriesInLanguages INNER JOIN
                          Categories ON CategoriesInLanguages.CategoryId = Categories.Id INNER JOIN
                          ProductstInCategories ON Categories.Id = ProductstInCategories.CategoryId 
        WHERE      (Products.IsEnable = 1)

Upvotes: 2

Views: 89

Answers (3)

codingbiz
codingbiz

Reputation: 26396

You can only have

Select col1, col2, col3 from table1

or

Select  @param1 = col1, @param2 =  col2, @param3 = col3 from table1 where id=value

and not both. Notice I added WHERE clause, your query should return 1 row to make use of @param

If you used the second option, you can then do the following

Select  @param1 as col1, @param2 as  col2, @param3 as col3

to return result

Upvotes: 0

cybertextron
cybertextron

Reputation: 10981

In MySQL, you can do something like: SELECT col1, col2, col3 FROM table1 WHERE col3 = param, but not mixing two operations at the same time. I would assume this rule applies to Sql Server. However, it's now clear what are you trying to do. Could you please be more specific?

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

You cannot mix the two operations. If you try it, the error message is self-explanatory.

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Upvotes: 1

Related Questions