CODEWITHSUNDEEP
mysqlsql
Seidl C.
Seidl C.

Reputation: 3

Select matching counted Values with other Tables Value using SQL

i am new to this forum and also to SQL and need to do a quick view at work. I searched for a topic like this, but didn't find the exact same problem...

I have 2 Tables: TableA and TableB

TableA

Name    NumberOfScreens
-----------------------
ABC1          5
ABC2          4
ABC3          7

TableB

Name   Date   Duration
------------------------
ABC1    x        xxx
ABC1    x        xxx
ABC1    x        xxx
ABC3    x        xxx
ABC3    x        xxx

My Problem now is to count the number of equal names in TableB and (thats how I think of it)

Select(*) from TableB where Count(Name) <> TableA.NumberOfScreens

So if ABC1 appears 5 times in TableB and TableA.NumberOfScreens of ABC1 = 5, i dont want it to appear in my SELECT. If ABC3 appears 3 Times in TableB and TableA.NumberOfScreens of ABC3 = 5, i need it to appear in my SELECT.

Can anyone help me?

Thanks :)

Upvotes: 0

Views: 59

Answers (1)

Serif Emek
Serif Emek

Reputation: 674

select A.Name, A.NumberOfScreens 
from TableA A join TableB B
on A.Name = B.Name
group by A.Name,A.NumberOfScreens 
having count(*) = A.NumberOfScreens

Edit:

select tb.Name, tb.Date, tb.Duration 
from TableB tb join 
(
select A.Name tmpName, A.NumberOfScreens tmpNumberOfScreen
from TableA A join TableB B
on A.Name = B.Name
group by A.Name,A.NumberOfScreens 
having count(*) <> A.NumberOfScreens
) tmp
on tb.name = tmp.tmpName

Upvotes: 1

Related Questions