-1

I need column-2 data of table-2 through the column-1 of table-1
Ex. if table-1(column-1) have 1,2,4 then output should be email1@email.com,email2@email.com,email4@email.com

Table -1      Table -2
Column-1     Column-1    Column-2
(varchar)    (int)     (varchar)
1,2,4       1      email1@email.com
2,3        2      email2@email.com
          3      email3@email.com
          4      email4@email.com

Using join I tried casting but doesn't work.
Tried Query : 
SELECT *
FROM Table-1
join Table-2
    on Table-1.Column-1 LIKE '%'+ cast(Table-2.Column-1 as varchar(MAX))+'%'
jarlh
  • 42,561
  • 8
  • 45
  • 63
jay sedani
  • 57
  • 9
  • which db and what error you are getting – PSK Feb 28 '19 at 07:18
  • sql server , error : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ CAST(Table-2.Column-1 AS varchar(MAX)) +'%' LIMIT 0, 25' at line 1 – jay sedani Feb 28 '19 at 07:19
  • If it is MYSQL replace `max` with, say, 10. – Salman A Feb 28 '19 at 07:31
  • Don't store lists of integers as comma separated strings. It's a breach of database design. It becomes harder to maintain the data, reduces performance, makes queries more complex and likely to break, prevents integrity constraints such as foreign keys, prevents tools such as indexes from being useful. Fix the problem (the design), not the symptom (how to write the join). – MatBailie Mar 02 '19 at 09:33

2 Answers2

1

first you should separate Column-1 from Table-1 like this then join tables like Table-1 join Table-2 on Table-1.Column-1=Table-2.separatedItem

cchajar
  • 114
  • 11
0

You need to cast as a char datatype, there is no varchar datatype that you can use in cast

MySQL supports limited data type for CAST, you can read more about the suppprted data types here

Try like following query.

SELECT * FROM 
`Table-1` t1
inner join  `Table-2` t2 on t1.`Column-1` LIKE 
                CONCAT('%', cast(t2.`Column-1` as char(100)),'%')
PSK
  • 17,547
  • 5
  • 32
  • 43
  • @MatBailie can you have a look to this https://stackoverflow.com/q/15368753/297322 – PSK Mar 02 '19 at 11:01
  • @MatBailie even it is not mentioned in the docs https://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast – PSK Mar 02 '19 at 11:03