CODEWITHSUNDEEP
sqlin-clause
ndemoreau
ndemoreau

Reputation: 3869

sql in clause doesn't work

I have a table with a column ancestry holding a list of ancestors formatted like this "1/12/45". 1 is the root, 12 is children of 1, etc...

I need to find all the records having a specific node/number in their ancestry list. To do so, I wrote this sql statement:

select * from nodes where 1 in (nodes.ancestry)

I get following error statement: operator does not exist: integer = text

I tried this as well: 

select * from nodes where '1' in (nodes.ancestry)

but it only returns the records having 1 in their ancestry field. Not the one having for instance 1/12/45

What's wrong?

Thanks!

Upvotes: 0

Views: 508

Answers (3)

Guffa
Guffa

Reputation: 700152

The in operator expects a comma separated list of values, or a query result, i.e.:

... in (1,2,3,4,5)

or:

... in (select id from SomeOtherTable)

What you need to do is to create a string from the number, so that you can look for it in the other string.

Just looking for the string '1' in the ancestry list would give false positives, as it would find it in the string '2/12/45'. You need to add the separator to the beginning and the end of both strings, so that you look for a string like '/1/' in a string like '/1/12/45/':

select * from nodes
where charindex('/' + convert(varchar(50), 1) + '/', '/' + nodes.ancestry + '/') <> 0

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

This sounds like a job for LIKE, not IN.

If we assume you want to search for this value in any position, and then we might try:

 select * from nodes where '/' + nodes.ancestry + '/' like '%/1/%'

Note that exact syntax for string concatenation varies between SQL products. Note that I'm prepending and appending to the ancestry column so that we don't have to treat the first/last items in the list differently than middle items. Note also that we surround the 1 with /s, so that we don't get false matches for e.g. with /51/ or /12/.

Upvotes: 2

Marco
Marco

Reputation: 57573

In MySQL you could write:

SELECT * FROM nodes
WHERE ancestry = '1' 
   OR LEFT(ancestry, 2) = '1/'
   OR RIGHT(ancestry, 2) = '/1'
   OR INSTR(ancestry, '/1/') > 0

Upvotes: 0

Related Questions