Why results affecting left join table shipyard

Here i wrote a query with left join table shipyard. There is no connection related to this table but its affecting the results when we joined or removed, The question is why ? Mainly its affecting OrderBook and TotalShips column.

    select a.sbwynum,
        a.sbnam,
        a.deleted,
        sum(if ((sh.statuscod = 'O' or sh.statuscod = 'S') and (left (
        condeldat, 4) = '2011' or left (adjdeldat, 4) = '2011' or left (
        deldat, 4) = '2011'), sh.cgt, 0)) as CurrCgt,
        count(if ((sh.statuscod = 'O' or sh.statuscod = 'S') and (left (
        condeldat, 4) = '2011' or left (adjdeldat, 4) = '2011' or left (
        deldat, 4) = '2011'), 1, NULL)) as CurrShips,
        count(if (sh.statuscod = 'O', 1, NULL)) as OrderBook,
        count(i开发者_运维百科f (sh.statuscod = 'S', 1, NULL)) as TotalShips,
        a.country as coucod,
        ct.counam,
        a.fulnam,
        a.status,
        a.stoclist,
        if (sh.statuscod = 'O', 1, 2) as StatusFlag
 from shipbuilder as a
      left join
      (select sbwynum, statuscod, condeldat, adjdeldat, deldat, cgt from
      `ship` s join shiptype st on s.wytypid = st.wytypid and st.forsearch
      = 'Y' and st.searchsb = 'Y' and deleted = 'N') sh on sh.sbwynum =
      a.sbwynum
      left join country ct on ct.coucod = a.country and ct.deleted = 'N'
      left join shipyard sy on a.sbwynum = sy.sbwynum and sy.deleted != 'Y' and
      sy.syclsid != 'B'
 where a.sbwynum != '' and
       a.deleted = 'N' and
       a.status != 'FV' and
       a.country = '365'
 group by a.sbwynum
 having a.deleted = 'N'
 order by sbnam

Thanks a lot.....

---

Instead of using joins use sub queries. At least for some of the trivial joins.

---

The question is why ?

Because you have more than one row in shipyard with the same sbwynum where deleted != 'Y' and syclsid != 'B'.

Update:

Here is an example trying to explain what I mean.

Table setup:

create table Table1
(
  ID int,
  Name varchar(10)
)

create table Table2
(
  ID int,
  IDFromTable1 int
)  

Table data:

insert into Table1 values (1, 'Name')

insert into Table2 values (1, 1)
insert into Table2 values (2, 1)

Count query without left join:

select count(T1.ID)
from Table1 as T1

Result:

-----------
1

Count query with left join to Table2

select count(T1.ID)
from Table1 as T1
  left outer join Table2 as T2
    on T1.ID = T2.IDFromTable1

Result:

-----------
2

---

Based on the comment, it means that you have a 1 to many relationship from shipbuilder to shipyard that falls within your join criteria. This should be solved with a select distinct.

Edit The distinct should be applied within your count statements