0

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;`
Community
  • 1
  • 1
conr404
  • 305
  • 2
  • 4
  • 19

1 Answers1

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.

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