Returning the first result from another table if statement is true-CodePudding

So I'm struggling to find the logic for the next problem:

I got 2 tables

TABLE A has the following column

Postalcode
1111
2222
3333
4444

TABLE B has the following column

Postalcode
1111AA
1111BB
1111CA
2222AA etc

What I would like to have is that if the Postalcodes first 4 numbers are found from Table A in table B, then I would like to have the first result of that postalcode from Table B (4digits 2letters).

e.g. if the postalcode in A is 1111 and substring(postalcode, 1, 4) of Table B is also 1111, then return the first result of that postalcode from Table B --> 1111AA

I can't seem to find the answer for this and I'm struggling for a while now.

Hope you guys have the solution for me.

CodePudding user response：

i have change the sample from @Ergest Basha with a virtual column and index

CREATE TABLE table_a (
Postalcode INT ,
KEY idx_sPortalcode (Postalcode)
);

INSERT INTO table_a VALUES 
(1111),
(2222),
(3333),
(4444);

CREATE TABLE table_b (
id INT,
Postalcode  VARCHAR(25),
sPostalcode INT  AS ( 0   Postalcode) STORED,
KEY idx_sPortalcode (sPostalcode)
);


INSERT INTO table_b (id,Postalcode) VALUES 
(1,'1111AA'),
(2,'1111BB'),
(3,'1111CA'),
(4,'2222AA');




SELECT * FROM table_b;


-- EXPLAIN
SELECT b.Postalcode
FROM table_a  a
INNER JOIN table_b b ON b.sPostalcode=a.Postalcode
WHERE a.Postalcode=1111
ORDER BY b.id ASC LIMIT 1;

CodePudding user response：

Something like this: MySQL

select b.Postalcode
from table_a  a
inner join table_b b on LEFT(b.Postalcode,4)=a.Postalcode
where a.Postalcode=1111
order by b.id asc limit 1;

Check the demo

SQL Server

select top(1) b.Postalcode
from table_a  a
inner join table_b b on LEFT(b.Postalcode,4)=a.Postalcode
where a.Postalcode=1111
order by b.id ;

Demo

Edit based on comments* I think you need something like below, but check @Bernd Buffen suggestion for performance:

WITH cte AS ( 
         SELECT  Postalcode, ROW_NUMBER() OVER ( PARTITION BY LEFT(Postalcode,4) ORDER BY id asc ) row_num
         FROM  table_b 
            )
SELECT  cte.Postalcode 
FROM table_a a  
INNER JOIN cte  on LEFT(cte.Postalcode,4)=a.Postalcode
WHERE row_num = 1 ;

Demo