Last post Aug 11, 2015 06:09 AM by devi.zerosoft
Aug 10, 2015 02:01 AM|devi.zerosoft|LINK
This is the concept I need to implement the SQL Query
-if user is under a city that has 0 products under it - home page should pull products from around the state
-if user is under a province that has 0 products under it - home page should pull listings from around the country
-if user is under a country that has 0 products under a section (i.e. no Service listings in US) - home page should pull from the entire product to fill the spaces
I have used the below where condtion to show product. It doesn't produce the correct result that I am expected
WHERE( (CityId = @CityId )
(@ProvinceStateId > 0 AND ProvinceStateId = @ProvinceStateId))
Please suggest me. I am stuck on this.
Aug 11, 2015 02:28 AM|devi.zerosoft|LINK
Please suggest any one. It is very urgent.
Thanks for your understanding!
Aug 11, 2015 03:54 AM|Krunal Parekh|LINK
Can you please provide your table structure with demo data so we can make query on our side. and please let use know how do you count the products for city , provenance etc. ? Is it a field or do you have data in another table that you find using subquery
? either way table structure and sample data would help much more.
Aug 11, 2015 05:00 AM|devi.zerosoft|LINK
Please verify the below search query that i have used previously. Could you please modify this query. Hope It would be helpful. otherwise you need I will give the table structure.
SELECT TOP 1000 ProductId, UserId
SELECT ProductId, Name, PriceSymbol, PriceType, Price, PriceFrom, PriceTo, Slug, Abbr, UserId, SellerName, ImageSrc, ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY NEWID()) as 'RowNum', CategoryTypeId
SELECT DISTINCT p.ProductId, p.Name, c.Symbol AS PriceSymbol, p.PriceType, p.Price, p.PriceFrom, p.PriceTo, p.Slug, c.Abbr,
CASE WHEN LEN(u.Username) > 0 THEN u.UserName ELSE CASE WHEN LEN(u.DisplayName) > 0 THEN u.DisplayName ELSE 'Anonymous' END END AS SellerName,
ct.ProvinceStateId, ct.CityId, u.AdditionalListingCity1, u.AdditionalListingCity2, u.AdditionalListingCity3,
FROM Product AS p
INNER JOIN ProductCategory AS pc ON p.ProductId = pc.ProductId
INNER JOIN Category AS cg ON pc.CategoryId = cg.CategoryId
INNER JOIN Currency AS c ON p.CurrencyId = c.CurrencyId
INNER JOIN [User] AS u ON p.UserId = u.UserId
INNER JOIN [Address] AS a ON u.AddressId = a.AddressId
INNER JOIN City AS ct ON a.CityId = ct.CityId
SELECT TOP 1 ProductImage.ImageSrc
WHERE ProductImage.ProductId = p.ProductId
AND ProductImage.ProductImageSizeId = 2
AND ProductImage.eff_end_dt is null
ORDER BY ProductImage.DisplayOrder
WHERE u.FirstName IS NOT NULL
AND p.eff_end_dt IS NULL
AND u.eff_end_dt IS NULL
AND (p.IsHidden = 0 OR ISNULL(p.IsHidden, 0) = 0)
AND (p.IsRotation = 1)
AND p.IsPublished = 1
AND p.productid IN (SELECT productid FROM dbo.ProductImage WHERE eff_end_dt IS NULL)
-- AND (@CategoryTypeId = 0 OR cg.CategoryTypeId = 1)
AND (@CategoryTypeId = 0 OR cg.CategoryTypeId = @CategoryTypeId)
--AND p.ProductId IN (SELECT ProductId FROM ProductCategory AS pc INNER JOIN Category AS c ON pc.CategoryId = c.CategoryId WHERE c.CategoryTypeId = @CategoryTypeId)
AND u.UserTypeId <> 3
AND (cg.CategoryTypeId = 2 OR (cg.CategoryTypeId = 1 AND p.Quantity > 0))
WHERE (((CityId = @CityId OR AdditionalListingCity1 = @CityId OR AdditionalListingCity2 = @CityId OR AdditionalListingCity3 = @CityId) AND @CityId > 0)
(@ProvinceStateId = 0 OR (@ProvinceStateId > 0 AND ProvinceStateId = @ProvinceStateId AND @CityId = 0)))
) AS top2EachSeller
WHERE RowNum <= 2
Looking forward your response!
Aug 11, 2015 05:53 AM|Nishantg|LINK
If I understood your requirement then you can use the below approach.
1. You need to write different query for each of your condition.
2. Take some variable and stores the value for the City,State,Country your User belongs to.
3. Check your final query whether the product is exist for the given City, if it does execute the query for the given City.
4. If data doesn't exist for given City, then check for State and execute the query for the states and same for your Country as well.
5. Final condition should not use any filter like City,State or Country.
I hope this will help you.
Aug 11, 2015 06:09 AM|devi.zerosoft|LINK
Thanks for your response!
But it will takes more time to execute the query.