check if a column contain the value from another Table's column

[selma147]

Hi,

I have a data model of 2 tables A and B:

[A]:

SELECT

Name,

Departement,

Profession

FROM "public"."A";

[B]:

SELECT

              Title

FROM "public"."B";

I would like to add a new column in Table A and test if the column Profession contain the content of the column Title from table B

The new column should be exactly the column Title but in table A.

For example:

If I have A.Profession: Responsible of HR and in B.Title I have HR value

The value of the new column should be HR.

Could you please help me with this?

Thank you in advance,

Regards,

Selma

[lblumenfeld]

Does table B contain only on row, or multiple rows? If the latter then do you mean

For each record in table A
   Search all the records in table B and if the value of A.Profession contains the value of B.Title from any record in B then
       Make A.NewColumn = B.Title

Let me know if this is what you're asking. Thanks.

[selma147]

Yes this is the requirements.
Thanks a lot,
Selma

[marcus_sommer]

Usually it's a job for a mapping, for example with something like:

[B]: mapping load Title as Lookup, Profession as Return;
        SELECT Title, Profession FROM "public"."B";

[A]: SELECT Name, Departement, Profession, applymap('B', Profession, 'Default') as New FROM "public"."A";

But in your case you will need a second column containing the return-values because only with one column Title = Profession or it does not match. I assume here that your description is just not complete.

Thinkable are also approaches which search for sub-strings but in this case you will need a defined logic which sub-strings will lead to a valid matching.

- Marcus