I have a table that has data that looks like this:
id oe
-- ---------------------------------------------------
1 04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
2 64526915083,64526953474
3 04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
4 F4DH8C607AA,F4DHAA
5 F4DH8C607AA,F4DHAA
6 04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
7 64526915083,64526953474
8 F4DH8C607AA,F4DHAA
9 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
11 04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
12 64526915083,64526953474
13 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14 F4DH8C607AA,F4DHAA
15 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
How can I write a query where I can pass an array of values and return rows where that column contains any of the values in the array?
This works because I get another value from a lookup table and I'd rather not do it that way as there is no index on the hol
column in the pubnet
table.
[please don't suggest adding one as I cannot modify DB]
SELECT * FROM pubnet
where hol in (select hol from id2hol where id in ('F3DH19860AA', 'F4DHAA'))
This would be the result set:
id oe
-- ---------------------------------------------------
4 F4DH8C607AA,F4DHAA
5 F4DH8C607AA,F4DHAA
8 F4DH8C607AA,F4DHAA
9 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
13 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14 F4DH8C607AA,F4DHAA
15 F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
hol
oroe
? ACREATE TABLE
statement showing names, data types and constraints is always best, and we can easily build a test case from it. – Erwin Brandstetter Apr 27 '18 at 2:56