1

I have 2 tables :

PRODUCTS
+----------+----------+----------+----------+
| id       |  title   |  content |   city   |
+----------+----------+----------+----------+

CITIES
+----------+----------+
| id       |  title   |
+----------+----------+

Where in my products table data stored like : (cities that a product belongs to separated by comma)

PRODUCTS
+----------+----------+----------+----------+
| id       |  title   |  content |   city   |
+----------+----------+----------+----------+
| 1        |  some    |  info    | 1,3,4,15

Now , how can i select products and all cities that it belongs to. And i want to search products by city's id too.

I've read some topics also this one , but mine is a bit different

0

I have a function that I keep on the master database for these cases. I will start by creating my function as per code below:

use master
go

create function dbo.getCities( @thecities varchar (100))
returns table
as 
return

WITH    cteTally
      AS (SELECT TOP 100
            ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
          FROM
            sys.all_columns AS AC CROSS JOIN
            sys.all_columns AS AC2  )

,RADHE2 (THECITIES)AS (
    SELECT
        LTRIM(SUBSTRING(','+@THECITIES+',',N+1,CHARINDEX(',',','+@THECITIES+',',N+1)-N-1)) AS THE_CITIES
    FROM
        cteTally C
    WHERE
        -- need the - 1 because cteTally strts with 1
        n < LEN(',' + @THECITIES + ',') AND
        SUBSTRING(',' + @THECITIES + ',', n, 1) = ','

)

SELECT * FROM RADHE2
GO 

then I will create my tables according to your question. I did not bother about indexes here, but you shoudl have a look at this, because this process is bound to be very process intensive.

SET NOCOUNT ON

DECLARE @PRODUCT TABLE (ID INT,
                    TITLE VARCHAR(100),
                    CONTENT VARCHAR(100),
                    CITY VARCHAR(100))



DECLARE @CITIES TABLE (ID INT,
                   TITLE VARCHAR(100))


INSERT INTO @PRODUCT VALUES (1,'PROD1', 'BANANAS', '1,2,4')
INSERT INTO @PRODUCT VALUES (2,'PROD2', 'APPLES', '3')
INSERT INTO @PRODUCT VALUES (3,'PROD3', 'STRAWBERRIES', '3,5')
INSERT INTO @PRODUCT VALUES (4,'PROD4', 'MANGO', '1,2,4')
INSERT INTO @PRODUCT VALUES (5,'PROD5', 'PEAR', '2,4')

INSERT INTO @CITIES VALUES (1,'CARACAS')
INSERT INTO @CITIES VALUES (2,'RIO')
INSERT INTO @CITIES VALUES (3,'TALLINN')
INSERT INTO @CITIES VALUES (4,'LIMA')
INSERT INTO @CITIES VALUES (5,'MOSCOW')

this gives me the following tables:

 select * from @PRODUCT
 select * from @CITIES

enter image description here

then, the final step, I use CROSS APPLY with the function, as you can see on the code below:

SELECT P.*
      ,C.TITLE
FROM @PRODUCT P 
CROSS APPLY MASTER.dbo.getCities( p.CITY) R
INNER JOIN @CITIES C ON R.THECITIES = C.ID
order by p.ID

and that gives the following result, which I believe is what you are looking for:

enter image description here

|improve this answer|||||
  • Wow , thanks man. this is perfect function :) – absfrm Oct 30 '14 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.