CODEWITHSUNDEEP
mysqlsqldatabase
Techfist
Techfist

Reputation: 4344

SQL view creation query help needed

I have two tables say,

Table 1(space)
----------------------------
_id |  number  | token
1      xy123456    assasasASAS
2      xy1234567   jkshdjsddsd


Table 2(watch)
----------------------------
_id  |  number  |
 1      1234567

I need a view like, this (basically join watch and space where _id are not same and space.number like %(watch.number)

View(integrate)
-------------------------------
_id  |  number    |  token
1       xy1234567    jkshdjsddsd

for which am executing below ddl, although its not throwing any error but not yielding required output as well, am really banging my head but not able to figure out issue, to be noted am not an sql guy so i might be doing some silly thing. Please help me out.

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `integrate` AS
    select 
        `watch`.`_id` AS `_id`,
        `space`.`number` AS `number`,
        `space`.`token` AS `token`
    from
        (`watch`
        join `space`)
    where
        ((`space`.`number` like ('%' + `watch`.`number`))
            and (`watch`.`_id` <> `space`.`_id`))

Upvotes: 0

Views: 20

Answers (1)

sgeddes
sgeddes

Reputation: 62841

A simple join will work for this:

select w.id, 
       s.number,
       s.token
from watch w
    join space s on w.id != s.id and s.number like concat('%',w.number)

Upvotes: 1

Related Questions