Getting rows of a table in single record

Question

I am completely new to programming I have a table with 2 column Key(Integer) and Values(Containing comma separated string) for example below

Table A
  Key   Values
  1     1,2
  2     3,4

I have another table again with 2 columns Key(Integer) and Value(single integer value)

Table B
  Key   Value
   5     4
   6     7
   7     2
   8     8

I am trying to get a all the Keys from table B whose values are not in Table A. Something like below .

Select * from B where Value not in (1,2,3,4)

Any suggestion or help is deeply appreciated.

Read this->http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Satwik Nadkarny, Jul 10 '14 at 19:18
That will tell you how to split the comma-delimited values. You can then use those values in your `IN` clause. — Satwik Nadkarny, Jul 10 '14 at 19:19

score 0 · Answer 1 · answered Jul 10 '14 at 19:22

Your tableA is in a very bad format. You are storing numbers as strings, instead of in a native format. Worse, you are storing lists in a string variable. SQL has a very useful mechanism for storing lists. It is called a table. The particular table structure you would want in this case is a junction table.

That said, sometimes you are stuck with data in this format. Let me assume that is true here. You can approach this by doing something like this:

select *
from tableb b
where not exists (select 1
                  from tablea a
                  where ',' || a.values || ','  like '%,' || b.value || ',%'
                 );

This is quite inefficient compared with a version using a junction table.

Also, this uses || to concatenate strings. This varies by database. It could be +. Or &, or even just the concat() function.

Getting rows of a table in single record

1 Answers1