Sql Query Join in Oracle

Question

I have Parent table and multiple child tables with foreign key constraint.

School Table

School ID EduDetails Genders Address_id  EDUTYPE
  1        2          M         3         FGN

And the child tables like

 Education Details
 EDU ID    EducationType
  2        Online

 AKA Name
 School Id   AKA Name
     1       Test School
     1       School Test

 Gender Table
 Gender ID   Gender Desc
      M      Male

I am using Left outer join for the parent and school table to fetch the results.

But My issue is, If AKA table has 5 counts matching the school Id and Gender table has only 1 records for that school Id.

So the results comes with 5 duplicate rows with school Information and also other child table information.

Is there any workaround to fix this issue. I tried using subquery and row_number over by function. But it is not working for me. Can anybody help me to solve this issue.

Thanks in advance for your time in looking this issue.

My required output should be like this

 School_id AKA Name     GenderDesc EductaionType
   1       Test School    Male       Online
   1       School Test

So I need to have Null values for the not matching records.

The problem is your query is producing a Cartesian Product. What are your desired results and then we can help construct your query. — sgeddes
– sgeddes, Commented Feb 15, 2013 at 17:29
Your "child tables" aren't all actually child tables. "Education Details" and "Gender Table" are actually parent tables -- that is, "School Table" is a child of them, not vice versa -- but it's probably best to think of them as "lookup tables". ("AKA Name", however, is indeed a child table.) — ruakh
– ruakh, Commented Feb 15, 2013 at 17:43

sgeddes · Accepted Answer · 2013-02-15 17:52:37Z

Since you want all the records in the AKA Name table, I've joined on that getting a Row_Number for each row. Then using that Row_Number, LEFT JOIN on the other tables.

SELECT S.SchoolId, 
  SA.AKAName,
  G.GenderName,
  ED.EducationType
FROM School s
  JOIN 
     (SELECT SchoolId, 
        AKAName, 
        ROW_NUMBER() OVER (PARTITION BY SchoolId ORDER BY AKAName) rn
      FROM SchoolAKA
      ) SA ON S.SchoolID = SA.SchoolId
  LEFT JOIN 
     (SELECT EDUID, 
        EducationType, 
        ROW_NUMBER() OVER (ORDER BY EducationType) rn
      FROM EduDetails
      ) ED ON S.EDUID = ED.EDUID AND SA.rn = ED.rn
  LEFT JOIN 
     (SELECT GenderId, 
        GenderName, 
        ROW_NUMBER() OVER (ORDER BY GenderName) rn
      FROM Genders
      ) G ON S.GenderId = G.GenderId AND SA.rn = G.rn

Here is the SQL Fiddle.

And here are the results:

SCHOOLID   AKANAME       GENDERNAME   EDUCATIONTYPE
1          School Test   Male         Online
1          Test School   (null)       (null)

Collectives™ on Stack Overflow

Sql Query Join in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related