How to Count Records from different table

Transact-SQL
21

So I should replace the IN in my code with a LEFT JOIN?

22

yah

SELECT * 
  FROM #voorstelling v 
  left join (SELECT r.Voorstellingsnummer 
FROM #reserveringen r 
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50
) r on r.Voorstellingsnummer = v.Voorstellingsnummer 
and Vestigingsnaam = 'Amsterdam' 
AND Filmnaam = 'Iron Man'
23

hi

i tried to do this

i hope it helps
:slight_smile:
:slight_smile:

drop create data.. 
drop table Table1
go 

create table Table1
(
aTime datetime ,
Number int 
)
go 

drop table Table2
go 

create table Table2
(
number int,
something varchar(5) 
)
go 

insert into Table1 select getdate()-6,1
insert into Table1 select getdate()-6,2
insert into Table1 select getdate()-6,3
insert into Table1 select getdate()-6,4
go

insert into Table2 select 1,'ok'
insert into Table2 select 2,'ool'
insert into Table2 select 2,'op'
insert into Table2 select 1,'lk'
go
SQL.. 
select a.atime from table1 a
join table2 b on a.Number < b.number

image

24

The query you provided puts out this:

9

But I don't think that's what should happen

25

try: -
SELECT *
FROM voorstelling v
WHERE v.Voorstellingsnummer NOT IN
(
SELECT r.Voorstellingsnummer
FROM reserveringen r
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r.Voorstellingsnummer
HAVING COUNT(*) >= 50
)
AND Vestigingsnaam = 'Amsterdam'
AND Filmnaam = 'Iron Man';

1 Like
26

MAN IT WORKS, I'm so grateful, BIG virtual hug, that works and I actually understand the code. Thank you ,thank you ,thank you

1 Like