Join syntax question

[ddeegan]

Hello

I am making a mistake that I cannot see, hopefully someone can correct me.

I want to have the same # of records returned when I join a table. What is the correct syntax? (should be below the comments I think)


SELECT count(*) from
MAIN_TABLE MT
INNER JOIN
FIRST_TABLE FT
ON MT.MASTER_CUSTOMER_ID = FT.MASTER_CUSTOMER_ID
AND MT.SUB_CUSTOMER_ID = FT.SUB_CUSTOMER_ID
INNER JOIN
SECOND_TABLE ST
ON MT.MASTER_CUSTOMER_ID = ST.MASTER_CUSTOMER_ID
AND MT.SUB_CUSTOMER_ID = ST.SUB_CUSTOMER_ID
INNER JOIN
THIRD_TABLE CUS_ADDRESS TT
ON TT.CUS_ADDRESS_ID = ST.CUS_ADDRESS_ID

-- This works fine
-- now I want to add a table that links to another table
-- and return the same records (the results from the first
-- three records will be duplicated) after I
-- add the following lines...
-- the table aliased as "FIFTH" is the same table aliased
-- as "MT"

LEFT JOIN
FOURTH_TABLE FOURTH
ON FOURTH.master_customer_id =MT.MASTER_CUSTOMER_ID
and FOURTH.sub_customer_id = MT.sub_CUSTOMER_ID
RIGHT JOIN
MAIN_TABLE FIFTH
ON FOURTH.related_master_customer_id =
FIFTH.master_customer_id
AND FOURTH.related_sub_customer_id=FIFTH.sub_CUSTOMER_ID


Thanks in advance
Dave

 

[rac2]

If the relationship between the tables is one-to-one, then COUNT(*) will yield the same value with or without the JOIN.

If the additional tables have zero or one row for each row in the basic table then COUNT(*) will be the same with the LEFT JOIN.

Using a RIGHT JOIN implies that the additional table may have more rows than the basic table. In that case COUNT(*) may be larger with the JOIN.

If the additional tables may have more than one row linked to each row in the basic table then COUNT(*) may be larger or smaller with the JOIN; and will be equal to or greater with the LEFT JOIN.