Recognise if row is Valid based on combination of conditions in another table

Question

I have table Inbound with ~2mil records, with the following structure

    Inbound table
    ID   campaign_id   Entry_status  Error_code     
    A1      1234            0            0
    B1      1234           -1           -1
    C1      4123            0           -15
    C1      4123            0            0

I also have a table Rules which is the list of all the combinations of Entry_status and Error_code which denote a valid entry in the Inbound table

    Rules table
    campaign_id   Entry_status  Error_code    
       1234            0            0
       4123            0           -15

I am trying to create a query which will allow my to list all the entries in Inbound which are valid based on the combinations Entry_status & Error_code in Rules

So far I have come up with this, but it is only giving me the invalid entries, and I also suspect it to be wrong.

SELECT * FROM Inbound
  WHERE ID not IN (
         SELECT ID FROM Inbound JOIN Rules
         on Inbound.campaign_id= Rules.campaign_id
         where Inbound.Entry_status = Rules.ENTRY_STATUS 
          and 
          Inbound.Error_code = Rules.Error_code
 )

It feels like I need to nest another query to take account of the combination of the 2 columns to produce a valid entry?

Had a look at this and this but not help as the validation criteria is a string, rather than a combination of columns

................................

Bonus

Is it possible to add true / false field in ìnbound which denotes if the records are valid based on the rule combination in rules

Can I run

      Update Inbound I
      SET I.valid = if(**select function** = True , 1 ,0 )

so Inbound has the valid and invalid entries flagged, rather than just another table

Select function courtesy of underscore-d

select distinct Inbound.*
from Inbound
inner join Rules on
Inbound.campaign_id = Rules.campaign_id
Where 
Inbound.Entry_status = Rules.Entry_status and
Inbound.Error_code = Rules.Error_code;`

score 1 · Accepted Answer · answered Jul 21 '15 at 16:06

1

select distinct Inbound.*
from Inbound
inner join Rules on
    Inbound.campaign_id = Rules.campaign_id
where -- or adding these conditions to the INNER JOIN would be the same
    Inbound.Entry_status = Rules.Entry_status and
    Inbound.Error_code = Rules.Error_code;

...or just change your not in to an in, since you got it completely the wrong way around! Although that nested version seems more convoluted than this.

answered Jul 21 '15 at 16:06

underscore_d

6,309
3
38
64

Havn't used SQL in a while and with such big data sets (for me) concerned about the errors. Thanks! – conr404 Jul 21 '15 at 16:19
Makes sense! Best to be cautious. Let me know if this does what you need, and if so, feel free to accept the answer :) – underscore_d Jul 21 '15 at 16:22
Would it be possible to set a boolean field in Inbound should the record be valid? Can you query true / false off select statements? – conr404 Jul 21 '15 at 17:20
1

Not familiar with MySQL specifically, but in SQL Server I would normally do this using a `merge` that lays out a source subquery applying all the necessary criteria, returns a set of matching ids and `case when` valid or invalid, and then `update`s the destination table according to these. I believe this thread should help you write a MySQL-compatible equivalent for that: http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query – underscore_d Jul 21 '15 at 17:49

Recognise if row is Valid based on combination of conditions in another table

1 Answers1