Use IN to compare Array of Values against a table of data

Question

I want to compare an array of values against the the rows of a table and return only the rows in which the data are different.

Suppose I have myTable:

| ItemCode | ItemName | FrgnName |
|----------|----------|----------|
| CD1      | Apple    | Mela     |
| CD2      | Mirror   | Specchio |
| CD3      | Bag      | Borsa    |

Now using the SQL instruction IN I would like to compare the rows above against an array of values pasted from an excel file and so in theory I would have to write something like:

WHERE NOT IN (
    ARRAY[CD1, Apple, Mella],
    ARRAY[CD2, Miror, Specchio],
    ARRAY[CD3, Bag, Borsa]
)

The QUERY should return rows 1 and 2 "MELLA" and "MIROR" are in fact typos.

Uueerdo · Accepted Answer · 2020-03-16 20:58:13Z

3

You could use a VALUES expression to emulate a table of those arrays, like so:

... myTable AS t
LEFT JOIN (
   VALUES (1, 'CD1','Apple','Mella')
        , (1, 'CD2', 'Miror', 'Specchio')
        , (1, 'CD3', 'Bag', 'Borsa')
) AS v(rowPresence, a, b, c)
ON t.ItemCode = v.a AND t.ItemName = v.b AND t.FrgnName = v.c
WHERE v.rowPresence IS NULL

Technically, in your scenario, you can do without the "rowPresence" field I added, since none of the values in your arrays are NULL any would do; I basically added it to point to a more general case.

answered Mar 16, 2020 at 20:58

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

beginnerspirit Over a year ago

Thanks @Uueerdo. Your solution should work fine. I tested it and I get "Incorrect syntax near the keyword 'LEFT'. (Line 2)" . This is how I wrote my sql: --- SELECT ItemCode,ItemName, FrgnName AS t LEFT JOIN ( VALUES (1,'00.LAM.01','LAMINA OTTONE SCHEDA 02.S00.01','FLASHER 2P 12V') ) AS v (rowPresence, a, b, c) ON t.ItemCode = v.a AND t.ItemName = v.b AND t.FrgnName = v.c WHERE v.rowPresence IS NULL

Uueerdo Over a year ago

@MirkoNardi where is the FROM myTable?

beginnerspirit Over a year ago

thanks for your help! This is how i rewrote the code for my purpose: SELECT ItemCode,ItemName, FrgnName FROM dbo.OITM AS t INNER JOIN ( VALUES ('00.LAM.01','LAMIN OTTONE SCHEDA 02.S00.01S','FLASHER 2P 12V'), ('00.C00.600','SCATOLONE INDUSTRIALE 600X400X500 H','TFFFT 22222 BC - BANCALE 70X40') ) AS v (a, b, c) ON t.ItemName = v.b OR t.FrgnName = v.c WHERE v.b <> t.ItemName OR v.c <> t.FrgnName

Uueerdo Over a year ago

@MirkoNardi That seems like is for a slightly different problem (but I am assuming what you posted was more of a representative case). One thing I notice with it though is that you should be able to do without the WHERE conditions if you change the ON to use a XOR rather than OR.

beginnerspirit Over a year ago

based on your experice would it be possible to convert this script to mysql? According to this its' not modern-sql.com/blog/2018-08/whats-new-in-mariadb-10.3

|

Collectives™ on Stack Overflow

Use IN to compare Array of Values against a table of data

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related