Why count is wrong for Books related to Authors?

Transact-SQL
,
1

I work on SQL server 2012 I face issue count is wrong for book s related to author

so why count for books related to author is wrong for author Ahmed

create table #books
(
BookId int,
BookName nvarchar(200),
AuthorId  int
)
create table #booksUpdate
(
BookId int,
BookName nvarchar(200),
AuthorId  int
)
insert into #booksUpdate
values
(119,'matlab',1),
(120,'3dmax',1),
(121,'c',1)
create table #Authors
(
AuthorId int,
AuthorName nvarchar(200)
)
insert into #Authors
values
(1,'Ahmed'),
(2,'Mohamed'),
(3,'Eslam')
insert into #books
values
(122,'c#',1),
(233,'Java',1),
(555,'c++',1),
(666,'photoshop',2),
(777,'asp.net',2),
(888,'python',2)

select a.authorName,count(b.BookName) as countBooks , count(bu.BookName) as countBooksUpdate,(count(bu.BookName) + count(b.BookName)) as Total from #Authors a 
left join #books b on a.AuthorId=b.AuthorId
left join #booksUpdate bu on a.AuthorId=bu.AuthorId
group by a.authorName
authorName countBooks countBooksUpdate Total
Ahmed 9 9 18
Eslam 0 0 0
Mohamed 3 0 3

result is wrong for Author Name Ahmed
it must be

authorName countBooks countBooksUpdate Total
Ahmed 3 3 6

so How to fix query to give correct result
Expected result is

authorName countBooks countBooksUpdate Total
Ahmed 3 3 6
Eslam 0 0 0
Mohamed 3 0 3
2

Why do you have such a design? Books and booksupdate, pretty much it's the same table. Why?

3

hi hope this helps !!

what i understood is that ..

left join #books b on a.AuthorId=b.AuthorId
left join #booksUpdate bu on a.AuthorId=bu.AuthorId

these two joins together is causing the problem ..
if you want to know why .. i can give explanation .. please let me know

i divided it into seperate parts ..

; with cte_books as 
(
	select 
	      a.authorName
		, count(b.BookName) as countBooks 
    from 
	   Authors a 
	      left join 
	   books b 
	      on a.AuthorId=b.AuthorId
	group by 
	      a.authorName
) , cte_update as 
(
	select 
	        a.authorName
		 ,  count(bu.BookName) as countBooksUpdate
    from 
	   Authors a 
	      left join 
	   booksUpdate bu 
	      on a.AuthorId=bu.AuthorId
    group by 
	   a.authorName
)
select 
       a.authorName 
	 , a.countBooks 
	 , b.countBooksUpdate
	 , a.countBooks + b.countBooksUpdate 
from 
   cte_books a 
      join 
   cte_update b 
      on a.authorName = b.authorName

image

4
5

Teach @harishgg1... explain the reason why or the OP will need to ask this question again in the future.

The link isn't bad but this is your opportunity to teach it here and for this specific problem.

6

thanks it solved

7

Jeff if you could explain it in a better way .. please let me know .. I would like to see

In table1 there are 2 records
in table2 there are 3 records
individually there are either 2 or 3 records
but when you join the two tables .. 2 * 3 = 6 records shows up

image
image566×582 5.66 KB

8

Perfect. I couldn't have done better.

The only thing to add is what these things are called...

"Accidental Cross-Join"
"Accidental Many-to-Many Join"
"Accidental Cartesian Product"
... or my favorite...
"Blind sided because I didn't know my data". :smiley:

9

Hi Jeff

what did you mean by ???

"Accidental Cross-Join"
"Accidental Many-to-Many Join"
"Accidental Cartesian Product"

Could you please explain ... ?? Thank You

this
"Blind sided because I didn't know my data
I am aware of

10

They're all just different names for the same thing. The word "accidental" is there because it certainly wasn't an intended thing in most people's code. "Cross-Join", "Many-to-Many", "Cartesian Product" are all different names for the same effect... an unwanted multiplication/duplication of rows based on a lack of additional criteria.

A more proper name is "Relational Multiplication".

11

oh ok

thanks Jeff .. gothca .. :+1: