Problem creating a query

[roedelfroe]

Hi everybody,

I'm facing a problem, which I can't solve.

Following situation:

Table 1: The ID is the foreign key of ID in Table 2

Row ID Value
100 1 DE1234567
101 4 CN4567890
102 3 NL1346799
103 3 US7654321
104 5 FR7658902



Table 2: In this table, the ID is the primary key and unique.

ID Country_Code
1 DE
2 NL
3 US
4 CN
5 US
6 CN


And now my problem:

As a result of a query I want to get following:

1.) If the first two characters of value in table 1 are matching the country code in Table 2 (join is obviously the ID), show value. This is the case for Rows 100, 101, 103.

2.) If the first two characters of value in table 1 aren't matching the country code in Table 1 and if there is no other value which does, show value. This is the case for Row 104.

Notice, that Row 102 should never be displayed because Row 103 ist matching criteria 1.)!

So the result I want to get is:

Value:

DE1234567
CN4567890
US7654321
FR7658902

Thank in advance

Roland

 

[gunjan14]

Hi,

I think you can use this code

(
 select a.value
 from   table1 a, table2 b
 where  a.id = b.id
 and    upper(substr(a.value,1,2)) = upper(b.country_code)
)
UNION ALL
(
 select value
 from   table1
 where  upper(substr(a.value,1,2)) 
        not in (select upper(country_code)
                from   table2
               )
);

Thanks and Regards,
Gunjan

 

[gunjan14]

Hi,

Sorry, the code should be

...
UNION ALL
(
 select value
 from   table1
 where  upper(substr(value,1,2)) 
        not in (select upper(country_code)
                from   table2
               )

Change a.value to value

Gunjan