I'm new to the group and the use of Toad so I thank you for your understanding with this question.
I have a query I'm running to provide me the date a record was received for a specific individual. When I run the query, it brings back upwards of 180 records, all with the same information.
I'm not sure if I'm doing the query wrong or too many join commands to get the information needed in a certain format.
Any assistance would be great.
It sounds like you have what's called a Cartesian Join to me.
That is when you don't join all of the primary key columns of all tables involved in your where clause.
So if table1 has primary key A, B and you want to join it to table2 with primary key A, B,
...you probably need to do something like:
select T1, T2
from table1 T1, table2 T2
where T1.A = T2.A
and T1.B = T2.B
failure to include all of the key columns in the join can result in "duplicate" rows returned like you describe.
Here is an example of the code, minus a few lines. If I need to provide the whole code, I can, just need to clean it up a little more.
select
c.Case_RCVD_DT,
c.office_display,
f.case_number,
f.LAST_NM||', '||p.FIRST_NM AS client_NAME,
e.case_STATUS,
e.Case_STATUS_BEGIN_DT,
from schema 1.table_1 c
left join table 2 f
on f.table 1 = c.table_1
left join schema2.table3 e
on e.Table3 = f.table4
Where
c.claim_rcvd_dt >= '01-Jun-2023'
and f.case_status_begin_dt >= '01-June-2023'
AND e.office_display = 'Los Angeles Office'
It's impossible for me to know for sure w/o knowing the primary keys on these tables, but I'm still guessing there is some join info missing somewhere in the ON clause.
It should probably look more like:
left join table 2 f
on f.primary_key_column = c.primary_key_column and ...... add other PK columns here if there are more than one.
...for each table.
Understand. I will do a clean up with a few of the table and column names for each I'm joining and send that over.
Thanks again...
It may be easiest to start with a single table, get the data you want, then join in a 2nd, get the data you want (no duplicate rows, etc), then move on to the 3rd. Do a little at a time.
Hope this helps out.
Schema A
Table: 1.1
Column Names:
Case_ID
Office_ID
File_ID
Last_Nm
First_Nm
Case_Status
Client_case_id
Schema B
Table 2.2
Column Names:
Case_ID
Employee_Name
Employee_ID
Case_Status_Begin_Date
Schema B
Table 3.3
Column Names:
Geography_ID
Office_Display
Region_Display
Schema C
Table 4.4 p
Column Names:
Client_ID
Last_NM
First_NM
Birthdate
Gender
Schema C
Table 5.5
Column Names:
Client_Case_id
Services
Last_Paid
Select
q.claim_rcvd_dt,
p.client_number,
p.Last_NM II ‘, ‘ IIp.First_NM AS Client Full Name
f.case_status
From schema 1.table 1.1 c
Left join schema b.table_2.2 f
Left join schema c.table_4.4 p
On p.client_id = c.client_case_id
Left join schema_c.table_5.5 q
On q.client_case_id = c.client_case_id q
Where
q.claim_rcvd_dt >= '01-Jun-2023'
and f.case_status_begin_dt >= '01-June-2023'
AND e.office_display = 'Los Angeles'
I don't see a join between table1.1 and 2.2. Not sure if that's the only problem.
From schema 1.table 1.1 c
Left join schema b.table_2.2 f
ON C.CASE_ID = F.CASE_ID
Left join schema c.table_4.4 p
On p.client_id = c.client_case_id
Left join schema_c.table_5.5 q
On q.client_case_id = c.client_case_id q
If that's not it, try rebuilding your query from scratch. Start with only one table. Join in one table at a time. Test it, make sure the data looks right, then add in another table, and continue adding one table at a time and making the joins right as you go. Using that technique, you should be able to spot where it's going wrong.
Also, are you sure you want Left Joins and not inner joins? Left joins do tend to return more rows. You can read the difference between them here SQL LEFT JOIN