Right join not generating all rows-CodePudding

I have two tables Factory and Site, the factory table has 10 records

Code      Site
Skirt     115
Skirt     116
Skirt     117
Skirt     118
Skirt     119
Skirt     120
Skirt     121
Skirt     122
Skirt     123
Skirt     124

and tbl_site has the following sites Site 115 116 117 118 119 120 121 122 123 124 125 But when I run the following query I only get 10 counts instead of 11

select count(*) from tbl_factory fr
right join tbl_site sit on fr.siteid = sit.siteid
where fr.code='Skirt'

CodePudding user response：

I would switch to a LEFT JOIN starting with the table you want all rows... essentially the same, but helps to start with ALL you want and OPTIONALLY getting the other.

This will show proper context to ensure all sites accounted for and which sites have a factory that makes skirts.

select 
      s.siteid,
      coalesce( count(f.siteid), 0 ) as HasFactory
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'
  group by
      s.siteid

Once that is confirmed, you can just do a count which will always give 11 sites since a left-join (not required having a match in the factory table). But you can also get a count of factories that DID make skirts

select 
      count(*) TotalSites,
      count( distinct f.siteid ) CountOfFactoriesMakingSkirts
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'

CodePudding user response：

try removing the where clause from your query since it filters the join results-the entire 11 rows- and returns only those with the code skirt -the 10 rows from the other table tbl_site or you can use the And as mentioned in this question why is my TSQL left join not working