Using a conditional select result to join with another table

Question

I have one table that has a column with activities that have either 7 or 9 digits. I have another table that has the description of those activities. Because of the way our database is setup, activities that have 9 digits correspond to specific tasks within a greater activity and to get the description of the greater activity you have to replace the last 2 digits with 00 and then find that description in the activity description table. I can easily do this in excel but am not sure how to do it in SQL. I don't have any code that isn't working to display because I haven't been able to get anything that looks like it's even close to working. Most subquery/join topics don't address modifying that results before using them to join with another table (or I'm just not understanding what they're saying).

Table examples and the desired output https://i.sstatic.net/8C50R.png

These are the steps I want to take

If a.activity_id is 7 characters, display activity_id as a new column "descr"

If a.activity_id is 9 characters, replace the last 2 with 00 and
display activity description of the new 9 characters in a new column "descr"

Join the new column "descr" with table b to display description of the generic activity (ending in 00)

Concat the 7 digit activity (regardless of how long it was initially) and the description of the activity

I can use substr and concat to display the new 9 digit # but I don't know how to only display the 9 digit # if activity_id is 9.

I also do not know how to use a modified select statement result to join with another table. Any help would be most appreciated. Thanks!

[1]:

Would you please show your current query, and provide sample data? — GMB
– GMB, Commented Mar 18, 2019 at 21:22
This would be much easier to understand, if you showed the tables (table names, column names, primary keys, and maybe even some data like you did for the result). From what you say it seems you are dealing with a bad data model. — Thorsten Kettner
– Thorsten Kettner, Commented Mar 18, 2019 at 21:34

Gordon Linoff · Accepted Answer · 2019-03-18 21:23:55Z

Your question is rather hard to follow -- it is hard to tell what is a table and what is a column, what is used for processing and what is needed in the result set.

I think the crux of the problem is how to express the join condition. Here is one method:

select . . . from a join c on c.activity_id = (case when len(a.activity_id) = 7 then a.activity_id else substr(a.activity_id, 1, 7) || '00' end)

Collectives™ on Stack Overflow

Using a conditional select result to join with another table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related