CODEWITHSUNDEEP
sqloracle-databaseone-to-many
RXC
RXC

Reputation: 1233

Select 1 of many rows linked to ID SQL

I am working on an SQL script in Oracle that grabs information from two tables and creates a new table. In one of the tables I want to retrieve data from, there is an account number column, and then for each account number there are 3 rows attached to it.

Account # | Descriptor
1           Cost Center: ASDF
1           Company: ASDF123
1           Fund: JKL
2           Cost Center: ASDF12
2           Company: ASDF456
2           Fund: JKL23

Here is my script:

create table mydb.test as
select substr(db1.table1.Account_Num, 1, 6) as Account_Number,
       substr(db1.table1.Descriptor, 1, 2) as Company_Description,
  from db1.table1 
       join db1.table2 
           on db1.table1.Account_Num = db1.table2.Account_Num

My question is, for the second line in the select statement, that sets the division in the new table, how can I grab only the "Company: " Desc row from each account number?

In the end, I want my final table to look like:

Account_Number | Company_Description
1                Company: ASDF123
2                Company: ASDF456

If anything is unclear or more info is needed, let me know.

Upvotes: 0

Views: 68

Answers (2)

Mohamad Mahmoud Darwish
Mohamad Mahmoud Darwish

Reputation: 4173

the following statements working fine for me:

create table mydb.test as
select substr(db1.table1.Account_Num, 1, 6) as Account_Number,
       substr(db1.table1.Descriptor, 1, 2) as Company_Description,
  from db1.table1 
       join db1.table2 
           on db1.table1.Account_Num = db1.table2.Account_Num
        order by Account_Number Desc

Upvotes: 1

Mark J. Bobak
Mark J. Bobak

Reputation: 14423

How about something like:

create table mydb.test as
select substr(db1.table1.Account_Num, 1, 6) as Account_Number,
       substr(db1.table1.Descriptor, 1, 2) as Company_Description,
  from db1.table1 
       join db1.table2 
           on db1.table1.Account_Num = db1.table2.Account_Num
 where db1.table1.descriptor like 'Company:%'
        order by Account_Number Desc;

Hope that helps.....

Upvotes: 1

Related Questions