Home » SQL & PL/SQL » SQL & PL/SQL » How avoid More than one Join With Same table (merged) (Oracle, Windows 10)
| How avoid More than one Join With Same table (merged) [message #690031] |
Fri, 20 September 2024 11:18  |
 |
Nicha
Messages: 34
Registered: March 2020
|
Member |
|
|
Hi,
I have two tables [Customers] and [Accounts]. The [Customers] table has the information about the Customers, while the [Accounts] table has all the accounts where they Intervene.
You can see both tables, and the desired result below:
I am using the Query:
Select A.Client_ID, A.Name, B.Account, B.Interv_Order,
--Cli_Interv. Data
Cli_Interv.Cliente_ID as Cli_Interv, Cli_Interv.Name as Name_Interv,
Cli_Interv.Interv_Order as Interv_Ord, Cli_Interv.Start_Date
From Customers A,
Accounts B,
--View to get Names for all Customers on an Account.
(Select A.Account, A.Cliente_ID, B.Name, A.Interv_Order, A.Start_Date
From Accounts A,
Customers B
where A.Client_ID = B.Client_ID
) Cli_Interv
where A.Client_ID = B.Client_ID and
B.Account = Cli_Interv.Account (+)
Order by A.Client_ID, B.Account
Tables Data:
CREATE TABLE Customers
(
Client_ID Number NOT NULL,
Name VARCHAR2(30) NOT NULL
)
insert into Customer values (10,'Olivia Newton-john');
insert into Customer values (11,'Noah Cyrus');
insert into Customer values (12,'Oliver Twist');
insert into Customer values (13,'Sophia Loren');
insert into Customer values (14,'Abby Elliot');
insert into Customer values (15,'Alex Moraes');
CREATE TABLE Accounts
(
Account VARCHAR2(2) NOT NULL,
Client_ID Number NOT NULL,
Interv_Order Number NOT NULL,
Start_Date DATE NOT NULL
)
insert into Accounts values ('A1',10,1,To_date('2024-02-13','YYYY-MM-DD'));
insert into Accounts values ('A2',14,1,To_date('2024-05-20','YYYY-MM-DD'));
insert into Accounts values ('A2',11,2,To_date('2024-05-20','YYYY-MM-DD'));
insert into Accounts values ('A2',15,3,To_date('2024-05-20','YYYY-MM-DD'));
insert into Accounts values ('A3',12,1,To_date('2024-08-05','YYYY-MM-DD'));
insert into Accounts values ('A4',11,1,To_date('2014-09-02','YYYY-MM-DD'));
The problem is that to get the data of the Customers, who intervene in an account, I am going, a second time, to the same tables with the View [Cli_Interv].
Is there any other way to get the same result without having to use the View [Cli_Interv]?
Thank you in advance.
|
|
|
|
| Re: How avoid More than one Join With Same table [message #690033 is a reply to message #690031] |
Fri, 20 September 2024 14:27  |
 |
Michel Cadot
Messages: 68693
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
A4 is missing in your result, is there a specific reason?
SQL> col name format a18
SQL> col name_interv format a18
SQL> break on client_id dup skip 1
SQL> select a.client_id, a.name,
2 b.account, b.interv_order,
3 c.client_id cli_invert, d.name name_interv, c.interv_order, c.start_date
4 from customers a
5 left outer join accounts b on b.client_id = a.client_id
6 left outer join accounts c on c.account = b.account
7 left outer join customers d on d.client_id = c.client_id
8 order by a.client_id, b.account, c.interv_order
9 /
CLIENT_ID NAME AC INTERV_ORDER CLI_INVERT NAME_INTERV INTERV_ORDER START_DATE
---------- ------------------ -- ------------ ---------- ------------------ ------------ -----------
10 Olivia Newton-john A1 1 10 Olivia Newton-john 1 13-FEB-2024
11 Noah Cyrus A2 2 14 Abby Elliot 1 20-MAY-2024
11 Noah Cyrus A2 2 11 Noah Cyrus 2 20-MAY-2024
11 Noah Cyrus A2 2 15 Alex Moraes 3 20-MAY-2024
11 Noah Cyrus A4 1 11 Noah Cyrus 1 02-SEP-2014
12 Oliver Twist A3 1 12 Oliver Twist 1 05-AUG-2024
13 Sophia Loren
14 Abby Elliot A2 1 14 Abby Elliot 1 20-MAY-2024
14 Abby Elliot A2 1 11 Noah Cyrus 2 20-MAY-2024
14 Abby Elliot A2 1 15 Alex Moraes 3 20-MAY-2024
15 Alex Moraes A2 3 14 Abby Elliot 1 20-MAY-2024
15 Alex Moraes A2 3 11 Noah Cyrus 2 20-MAY-2024
15 Alex Moraes A2 3 15 Alex Moraes 3 20-MAY-2024
[Updated on: Fri, 20 September 2024 14:31] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How avoid More than one Join With Same table [message #690036 is a reply to message #690035] |
Mon, 23 September 2024 04:53 |
cookiemonster
Messages: 13943
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
@Nicha - If you run your query (once you've fixed the typos) it leaves out Sophia Loren, and that happens because you didn't outer join Accounts A.
You should have done this:
Select A.Client_ID, A.Name, B.Account, B.Interv_Order,
--Cli_Interv. Data
Cli_Interv.Client_ID as Cli_Interv, Cli_Interv.Name as Name_Interv,
Cli_Interv.Interv_Order as Interv_Ord, Cli_Interv.Start_Date
From Customers A,
Accounts B,
--View to get Names for all Customers on an Account.
(Select A.Account, A.Client_ID, B.Name, A.Interv_Order, A.Start_Date
From Accounts A,
Customers B
where A.Client_ID = B.Client_ID
) Cli_Interv
where A.Client_ID = B.Client_ID (+) and
B.Account = Cli_Interv.Account (+)
Order by A.Client_ID, B.Account
a) Get all customers no matter what.
b) For each customer get all the accounts they are on - so outer join to accounts as they may not have any (Sophia)
c) For each account they are on get all the accounts entries for that account - so outer join accounts to itself, can't be inner as that would make b) inner
d) For each account entry get the name of the customer - so outer join back to customers - so outer join accounts back to customers, again can't be inner as that would make c) inner.
You tried to skip one of the outer joins you needed by doing an inline view but really that's functionally the same as Michel's version and oracle may well get the data in the same way.
You're worried about performance, but when dealing with outer joins the first question is - does the functionality require them? Your functionality does.
So do you have a performance issue, or are you just worried you'll get one because you're using outer-joins?
If you do have an issue feel free to post details here and we'll see if we can help. If you don't know, check, because Michel's query may run just fine.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 01:13:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
