Error comparing and fetching records in SQLite-CodePudding

I have this table

table1

---------------
nums          |   
---------------
1             |
2             |
8             |
32            | 
11            |
31            |
28            |
17            |
38            | 
14            | 
--------------

then I have this table

table2

--------------------
DRAWNUMBER|n1|n2|n3|
--------------------
39494     |2 |23|34|
28288     |8 |12|11|
23000     |11|32|20|
90000     |14|28|38|
30000     |22|18|11|
10000     |32|39|18|
-------------------

with this query I fetch the numbers of the main draw(Grouped)from table2

SELECT (" "||n1||"-"||n2||"-"||n3 )NUMBERS FROM(SELECT n1,n2,n3, max(DRAWNUMBER) FROM table2)

Results:

----------
NUMBERS  |   
---------|
14-28-38 |
----------

Now what I need to do is to bring in table 1 only the numbers that are in table 2.

With this query I try to make a comparison but I get all the numbers from table 2.

SELECT nums from table1 WHERE EXISTS(SELECT (" "||n1||"-"||n2||"-"||n3)FROM(SELECT n1,n2,n3, max(DRAWNUMBER) FROM table2))

Results:

---------------
nums          |   
---------------
1             |
2             |
8             |
32            | 
11            |
31            |
28            |
17            |
38            | 
14            | 
--------------

Expected:

---------------
nums          |   
---------------
14            |
28            | 
38            | 
--------------

If instead I want to fetch the numbers from table1 that ARE NOT in table2 it simply returns empty.

SELECT nums from table1 WHERE NOT EXISTS(SELECT (" "||n1||"-"||n2||"-"||n3)FROM(SELECT n1,n2,n3, max(DRAWNUMBER) FROM table2))

Results:

---------------
nums          |   
---------------
              | 
--------------

I don't know what is wrong with the query. I have already changed the EXISTS to = and I have also used operator <> but it does not meet my expectations. Thanks in advance.-

CodePudding user response：

You can join table1 to the query:

SELECT n1, n2, n3, MAX(DRAWNUMBER) FROM table2

that returns the numbers of your main draw:

SELECT t1.*
FROM table1 t1 
INNER JOIN (SELECT n1, n2, n3, MAX(DRAWNUMBER) FROM table2) t2
ON t1.nums IN (t2.n1, t2.n2, t2.n3)
ORDER BY t1.nums;

Or change IN to NOT IN to get the numbers from table1 that are not returned by the query.

See the demo.