Column contains values from other table columns

user_3240545 0
Viewed 77

I have a table Data as below:

Name    Address     Country
A       PO 123 AF   Afghanistan
B       PO 23 AFG   AF
C       PO IND df1  AFG
D       PO 12345    USA

I would like to get all records which has Address different from Country. In above example, I need record 3 (Name = C) as Address has "IND" and Country is "AFG".

Note:

  1. Country can be ISO-2, ISO-3 or Description (Ex. "IN", "IND" or "India")
  2. Address may or may not contain country name - if it contains, it could be ISO-2, ISO-3 or Description.

  3. Country detail is stored in a separate Country table and has all countries stored as -

    Id | Iso2 | Iso3 | Description

I am sure,

  1. First, we will need to check if Address contains ISO-2, ISO-3 or Description of any country - Address is a free text and country can be anywhere in the text or country may not even be present in Address - in such case, we need to skip those records.
  2. Then, if Address has country detail, we need to check if it is different from Country column (which again may contain ISO-2, ISO-3 or Description)

Thanks in advance for any help.

join mysql sql algorithm
1 Answers

Do you want the USA line in your result or not ?

SQL Fiddle

MySQL 5.6 Schema Setup:

Query 1:

select * 
from Data
inner join country 
    on (
        Data.country = country.Iso2
        or Data.country = country.Iso3
        or Data.country = country.Description
        )
Where 

    Data.Address NOT like concat('%',country.Iso2,'%')
    and Data.Address NOT like concat('%',country.Iso3,'%')
    and Data.Address NOT like concat('%',country.Description,'%')

Results:

| Name |    Address | Country | Id | Iso2 | Iso3 |              Description |
|------|------------|---------|----|------|------|--------------------------|
|    C | PO IND df1 |     AFG |  1 |   AF |  AFG |              Afghanistan |
|    D |   PO 12345 |     USA |  3 |   US |  USA | united states of america |

Query 2:

select Data.*, country.*
from Data
inner join country 
    on (
        Data.country = country.Iso2
        or Data.country = country.Iso3
        or Data.country = country.Description
        )
left join country ca
    on (
        Data.Address like concat('%',ca.Iso2,'%')
        or Data.Address like concat('%',ca.Iso3,'%')
        or Data.Address like concat('%',ca.Description,'%')
        )
Where 
    Data.Address NOT like concat('%',country.Iso2,'%')
    and Data.Address NOT like concat('%',country.Iso3,'%')
    and Data.Address NOT like concat('%',country.Description,'%')
    and ca.id IS NOT NULL

Results:

| Name |    Address | Country | Id | Iso2 | Iso3 | Description |
|------|------------|---------|----|------|------|-------------|
|    C | PO IND df1 |     AFG |  1 |   AF |  AFG | Afghanistan |
user_55462670
Related