Mysql – Select products and include all cities it belong to

MySQLsubquery

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

Best Answer

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

Related Question