How to match value of a table to another and create a new column in mySQL?-CodePudding

I am new to SQL and have been stuck at this one part of my work/assg for a long time and would really appreciate some guide/input!

I have 2 tables. The first table has a codepath that corresponds to the ordercode in the second table, if it matches and the flag is INCL, I should have another column that puts the itemcode specified in table 2 into table 1 and if it's EXCL, i should not include it.

Table1:

| code   | codepath       |
|:----   |:------:        |
| ABC12  | something/NPP3 |
| ABC13  | something/NPP4 |

Table2:

| itemcode | ordercode | flag |
|:----     |:------:   |-----:|
| CASH     | NPP3      | INCL |
| EXCASH   | NPP4      | EXCL |

Result:

| code   | codepath      | tag |
|:----   |:------:       | -----:|
| ABC12  | something/NPP3| CASH |
| ABC13  | something/NPP4| EXCASH |

There are many rows and this is just one example. Not sure if I should join the table but I dont think so as the number of rows dont match and there is no common column as well. Would greatly appreciate any help or guidance!

CodePudding user response：

can you check this?

select t1.code, t1.codepath, t2.itemcode as tag
from
table1 t1 join table2 t2 on SUBSTRING_INDEX(t1.codepath, '/', -1) = t2.ordercode

CodePudding user response：

You would do better to use codepath as the joining column. It should therefore be indexed, I suggest as a foreign key.
You could join using concat or substring but it is better to avoid functions in joins and enforce consstency with foreign keys. Where possible it is better to use an integer for the primary key to speed up the table scans and indexing.

create table table2 (
itemcode varchar(10) primary key,
ordercode char(5) unique,
flag char(4) );
create table table1 (
code char(5) primary key,
codepath char(5) ,
foreign key fk_codeparth (codepath)
references table2(ordercode));
insert into table2 values
('CASH','NPP3','INCL'),('EXCASH','NPP4','EXCL');
insert into table1 values
('ABC12','NPP3'),('ABC13','NPP4');
select
  t1.code,
  t1.codepath,
  t2.flag tag
from table1 t1
join table2 t2 on t1.codepath = t2.ordercode;

code  | codepath | tag 
:---- | :------- | :---
ABC12 | NPP3     | INCL
ABC13 | NPP4     | EXCL

*db<>fiddle here727c5a30c04725d5486acdc)