Concatenation query

[moin73]

How to get following output from following 3 tables ?

customer_id company stocks
111 c11 s11
c12 s12

112 c13 s12
c11 s13
c14 s14

Table structure

customer : customer_id, customer_name

company : company_id, company_name, customer_id

stock: stock_id, stock_name, customer_id

Regards

 

[SantaMufasa]

Moin,

First, the relationships amongst your tables don't seem to make any business sense of which I am familiar: Since you have "customer_id" as a foreign key on the rows of the "stock" table, this implies that "Each stock is for one (and only one) customer." Additionally, since the "company" table has a foreign key ("customer_id") to the "Customer" table, this implies: "Each company has one and only one customer." Don't these relationships seem bogus to you? If I have misinterpreted your data, please correct me by restating the correct meaning of your data.

Second, you have two data errors:

1) As your data structures imply above, "Each company has one and only one customer," yet you show Company C11 appearing under both Customer 111 and Customer 112. So, to produce the results in the format you want with the (very suspicious) data relationships you imply, then we must change Company C11 under Customer 112 to Company C15, for example.

2) Your data structure also implies, "Each stock is for one (and only one) customer." Yet, Stock S12 is for both Customer 111 and Customer 112. So, again, to make your data work, we shall change Stock S12 under Customer 112 to Stock S15.

Then lastly, because your data relationships are spurious, the results of data that reflect your format and content, above, result in "unholy"/spurious relationships:

Section 1 -- Pertinent contents of your data tables:

select * from customer;

CUSTOMER_ID
-----------
        111
        112

select * from company;

Company_ID CUSTOMER_ID
---------- -----------
c11                111
c12                111
c13                112
c15                112
c14                112

select * from stocks;

STOCK_ID CUSTOMER_ID
-------- -----------
s11              111
s12              111
s13              112
s14              112
s15              112

Section 2 -- SQL Query that "tries" to produce your results:

select cu.customer_id, co.company_id company, s.stock_id stock
from customer cu, company co, stocks s
where s.customer_id = cu.customer_id
  and co.customer_id = cu.customer_id;

CUSTOMER_ID COMPANY STOCK
----------- ------- -----
        111 c11     s11
        111 c12     s11
        111 c11     s12
        111 c12     s12
        112 c13     s13
        112 c15     s13
        112 c14     s13
        112 c13     s14
        112 c15     s14
        112 c14     s14
        112 c13     s15
        112 c15     s15
        112 c14     s15

So there is really no logical way to produce the results you want because your database design is faulty. If you design your tables to reflect your true business situation, then I am certain we can produce the results you want.

We can help you with your database design if you can specify/restate your true business-application relationships amongst your tables (business entities) in a way that makes business sense.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[moin73]

Thanks, appreciate your response with details.

Actually, following is the script I m using to break company associate with respect to employee.

CURSOR c1
IS
SELECT a.employee_id, d.description, c.company_name
TRIM (c.company_name)
FROM analyst_research a, research b, company c, industry d
WHERE a.research_id = b.research_id
AND b.company_id = c.company_id
AND b.industry_id = d.industry_id
AND b.business_unit_code = d.business_unit_code
AND employee_id IN
(SELECT TRIM (
pkg_3des_encrypt.fn_decrypt (
analyst_holding.analyst_employee_id,
16,
8
)
)
FROM analyst_holding
WHERE analyst_holding.deactivated_indicator = 'F')

OPEN c1;
FETCH c1 INTO v_employee_id, v_industry_desc, v_company_name;
v_old_employee_id := v_employee_id;
v_old_industry_desc := v_industry_desc;
v_old_company_name := v_company_name;
v_output_1 := v_industry_desc
|| ':'
|| CHR (10)
|| v_company_name;
LOOP
FETCH c1 INTO v_employee_id, v_industry_desc, v_company_name;
EXIT WHEN c1%NOTFOUND;

IF TRIM (v_old_employee_id) != TRIM (v_employee_id)
THEN
INSERT INTO temp_report (employee_id, coverage_information)
VALUES (v_old_employee_id, v_output_1);

v_old_employee_id := v_employee_id;
v_old_industry_desc := v_industry_desc;
v_old_company_name := v_company_name;
v_output_1 :=
v_industry_desc
|| ':'
|| CHR (10)
|| v_company_name;
ELSE
IF v_old_industry_desc != v_industry_desc
THEN
v_old_industry_desc := v_industry_desc;
v_output_1 := v_output_1
|| CHR (10)
|| CHR (10)
|| v_industry_desc
|| ':'
|| CHR (10)
|| v_company_name;
ELSE
v_output_1 := v_output_1
|| CHR (10)
|| v_company_name;
END IF;
END IF;
END LOOP;

-------------
Here is output, I want to see
EMPLOYEE COVERAGE
-------- ------------------
1063 Business Services:
COMPANY_TES

1064 Business Services:
COMPANY_TES

1069 Canadian Media:
Quebecor World

 

[SantaMufasa]

So, Moin, do you have what you want or do you need more help from us?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[moin73]

I need to know a solution for script, I posted earlier.

Appreciate your help.

Thanks

 

[SantaMufasa]

Sorry...Please pardon my dimness...what "solution" do you need? What specifically (in the script you posted earlier) are you having problems with? The script has no syntax errors and is producing results. Are the results not what you want? What is missing from the results?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[moin73]

Eventhough script is running and producing results.

But it's very slow with thousand of rows.

Is there any way to tune it ?

 

[SantaMufasa]

Yes, Moin, by far, the best first tuning step to make any long-running query run faster is to ensure that indexes exist on each of the columns in your WHERE clause. A good second step is to ensure that you have current STATISTICS for each of the tables involved in the query.

Implement those two steps, re-run the query, then report back on whether or not your performance is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.