CODEWITHSUNDEEP
sqlitesql
Salvatore Ucchino
Salvatore Ucchino

Reputation: 727

SQLite select record where a value is within a string

I have a DB structured like this:

TABLE1

(_id INTEGER PRIMARY KEY AUTOINCREMENT,

titolo VARCHAR(50) NOT NULL,

autore VARCHAR(50) NOT NULL,

id_categoria VARCHAR(20) NOT NULL,

testo TEXT,

UNIQUE(titolo,autore))

TABLE2

(_id INTEGER PRIMARY KEY AUTOINCREMENT,

titolo VARCHAR(50) NOT NULL UNIQUE,

attiva INTEGER(1) DEFAULT(0))

How can I select a row where table2._id is within table1.id_categoria?

Tthe table1.id_categoria field is something like this "1-2-3-..." I want to check if the integer field _id of table2 is in field string id_categoria of table1.

Upvotes: 0

Views: 177

Answers (4)

Umang Mehta
Umang Mehta

Reputation: 1507

LS-dev thats not correct answer.... see the screenshot:

enter image description here

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

SELECT * FROM table2 JOIN table1 ON "-"||table1.id_categoria||"-" LIKE "%-"||table2._id||"-%";

Upvotes: 0

Umang Mehta
Umang Mehta

Reputation: 1507

Try this:

select table2.*,table1.id_categoria from table1,table2 
where table1._id=table2._id and 
instr(table1.id_categoria, table2._id)

Check this:

Upvotes: 0

Umang Mehta
Umang Mehta

Reputation: 1507

Locate function in mysql works fine, you can experiment it with join queries with following example:

select * from table1,table2 where locate(table2._id,table1.id_categoria)

For the join perspective, you can also use something like

select * from table1,table2 where 
locate(table2._id,table1.id_categoria) and 
table1.titolo=table2.titolo

Upvotes: -1

Related Questions