What is the correct order in which tables should be joined

Question

I have three tables

TABLE 1 PLANS -> Has all the plans information

COLUMNS:

• PLAN_ID
• PLAN_NAME
• OTHER_DETAILS

(PLAN_ID is the PRIMARY KEY)

TABLE 2 REGISTER ->

COLUMNS:

• RUN_ID
• PLAN_ID

• REGISTER_DETAILS

  (RUN_ID AND PLAN_ID) is the primary key

  TABLE 3 ELECTION -> Columns

• RUN_ID

• PLAN_ID

• ELECTION_DETAILS

  (RUN_ID AND PLAN_ID) is the primary key

PLAN_ID could be present in either REGISTER (or) ELECTION (or) BOTH tables.

For an input RUN_ID , I need to pick rows in the below format such that if a plan has only register details only REGISTER_DETAILS is picked.

If a plan has both REGISTER_DETAILS and ELECTION_DETAILS then both the details should get returned.

Report Format:

RUN_ID PLAN_ID REGISTER_DETAILS ELECTION_DETAILS

Solution

I tried by joining the tables in below format:

SELECT
 ..
 FROM 
 PLANS A

  LEFT JOIN REGISTER B

  ON (A.PLAN_ID = B.PLAN_ID

      AND B.RUN_ID = 'Input Run Id')

  LEFT JOIN ELECTION C

  ON (A.PLAN_ID = C.PLAN_ID

      AND C.RUN_ID = 'Input Run Id')

But this is also returning plans that are not present in REGISTER and ELECTION tables.

Can someone please tell what is wrong with the query?

Answer 1

Add

WHERE B.PLAN_ID IS NOT NULL OR
      C.PLAN_ID IS NOT NULL

to the end of your query.

Best of luck.