SQLTeam.com | Weblogs | Forums

How to Count Records from different table

#1

So I'm trying to filter some records out of my select inside a form on my website.
Problem is that I currently have this setup:

Database
Table1
Time
Number
Table2
Number
Something

I'm trying to filter time by checking how many records show up of number in table2
So basically I select time from table1 where number is less than in table2

How do I actually put that in sql? I currently have this but it isn't working:

SELECT Time FROM Showcase WHERE  Number IN (SELECT COUNT(Number) 
FROM Build WHERE COUNT(Number) < 50);

it currently doesn't end up with anything but in theory it should show the times from the records where the count of the value of number is less than 50

so if Number = 40 and it gets counted 70 times it shouldn't show up.

but if Number = 30 is counted 29 times it should show up

can anyone tell me what is going wrong?

0 Likes

#2

A little confusing: -
SELECT Time
FROM Showcase S
WHERE S.Number IN (SELECT B.Number
FROM Build B
WHERE B.Number = A.Number
GROUP BY B.Number
HAVING COUNT(*) < 50);

0 Likes

#3

Can you tell me why you use WHERE B.Number = A.Number and not B.Number = S.Number

Anyway i think you meant to last one so I'll try if it works

0 Likes

#4

So I just add your suggestion to my code but it's not counting the contents from the table in the subquery.

here is the entire query from my actual server:

SELECT Tijd FROM voorstelling v WHERE Vestigingsnaam = '$geselecteerdvestiging' AND Filmnaam = '$geselecteerdfilmnaam' AND v.Voorstellingsnummer IN (SELECT r.Voorstellingsnummer FROM reserveringen r WHERE r.Voorstellingsnummer = v.Voorstellingsnummer GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50)
0 Likes

#5

Is there a relationship between showcase and build?
Instead of
v.Voorstellingsnummer IN (
try
v.Voorstellingsnummer EXISTS (

0 Likes

#6

What is it doing or not doing that makes you believe its not counting in the subquery

0 Likes

#7

Yes there is a relation but showcase is actually named voorstelling and build is actually named reserveringen in the official database. Voorstellingsnummer exists in both those tables. I'll try the EXISTS though and report back

EDIT:
Change IN to EXISTS but that gives the same result (nothing)

0 Likes

#8

Because when I look in the select it's only showing records that were counted in the reservingen because it should also show 1 record from the table voorstelling.

In the subquery it's counting how many times any value of voorstellingsnummer occurs in table reserveringen. That will create a column with results for every value of Voorstellingsnummer. Then it should also do the same in the table voorstelling and then make one column with everything added up so if Voorstellingsnummer = 5
Occurs 5 times in reserveringen and 3 times in voorstelling then the Total counted amount of Voorstellingsnummer = 5 should be 8 but currently it's only counting the ones in reserveringen so it would display 5 but as some values of Voorstellingsnummer don't exist in the table for reserveringen it displays Nothing if I'm looking for those because it things they don't exist in the table reserveringen (and that's true because they exist in the table Voorstelling)

0 Likes

#9

works for me

create table #voorstelling(Tijd int, Vestigingsnaam varchar(50), 
Filmnaam varchar(50), Voorstellingsnummer int);
insert into #voorstelling
select object_id, name, reverse(name), object_id * 2 
from sys.objects where object_id between 1 and 20


create table #reserveringen(Voorstellingsnummer int);
insert into #reserveringen
select object_id * 2 from sys.objects where object_id between 1 and 20


SELECT Tijd 
  FROM #voorstelling v 
WHERE Vestigingsnaam = 'sysrscols' 
AND Filmnaam = 'slocsrsys' 
AND exists (SELECT r.Voorstellingsnummer 
FROM #reserveringen r 
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer 
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50)

  drop table #reserveringen
  drop table #voorstelling
0 Likes

#10

I ran the code like this:

SELECT Tijd FROM #voorstelling v WHERE Vestigingsnaam = 'Amstedam' AND Filmnaam = 'Avengers' AND exists (SELECT r.Voorstellingsnummer FROM #reserveringen r WHERE r.Voorstellingsnummer = v.Voorstellingsnummer GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50);

it gave me this error in phpmyadmin:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

0 Likes

#11

oh this is MySQL and not Microsoft Sql Server?

0 Likes

#12

Yes, I'm sorry if I didn't mention that clearly

0 Likes

#13

:tired_face: change AND exists to AND Voorstellingsnummer in
and try

0 Likes

#14
SELECT Tijd, Voorstellingsnummer, COUNT(Voorstellingsnummer)
FROM voorstelling v 
WHERE Vestigingsnaam = 'Amsterdam' 
AND Filmnaam = 'Avengers' 
AND (SELECT r.Voorstellingsnummer 
FROM reserveringen r 
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer 
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50)

It now outputs at least something which is good. However it's only outputting 1 record while it should output 2.

This is what the voorstelling table looks like:

1

and this is what the reserveringen table looks like:
(See other reply as I can only send 1 per post)

Voorstellingsnummer 5 occurs 2 times in the reserveringen table while it occurs only once in the voorstelling table.

the total occurrences should be 3
However Voorstellingsnummer=4 also has Vestigingsnaam=Amsterdam and Filmnaam=Avengers

so Voorstellingsnummer=4 should have 1 occurrence when counted and should thus also show up but it doesn't

0 Likes

#15

2

0 Likes

#16

This is different to your opening post and all solutions provided won't do what you are asking.
How many entries for one number are you expecting back.
Is that: -

  1. Only time entries from Table1?
  2. Are you only wanting the last time?
  3. If there are 40 entries of number in Table1 and 11 entries in table2 you wanting nothing returned?
  4. If there are 40 entries of number in Table1 and 5 entries in table2 you wanting 45 entries returned or only 40 from table1 or one entry with the last time etc?
  5. What output do you expect to see?
0 Likes

#17

I only want Tijd entries from Table1.
However that Tijd is connected with a Voorstellingsnummer and I only want to show Tijd entries if that Voorstellingsnummer from Table1 occurs less than 50 times in Table2

so for example if there was (in table1)
Voorstellingsnummer 1 (occurs 67 times in Table2)
Voorstellingsnummer 2 (occurs 31 times in Table2)
Voorstellingsnummer 3 (occurs 0 times in Table2)
Voorstellingsnummer 4 (occurs 108 times in Table2)
Voorstellingsnummer 5 (occurs 25 times in Table 2)

Then the result of the query should only show
Tijd for 2, 3 and 5 as only those occur less than 50 times in Table2

My problem however that in the current state my query would only show 2 and 5 because the query doesn't show Tijd for Voorstellingsnummer if it isn't in Table2.

I'm not sure if I'm being clear (english is not my native language) so if anything is still a bit unclear please tell me.

0 Likes

#18

SELECT Time
FROM Showcase A
WHERE A.Number IN (SELECT B.Number
FROM Build B
WHERE B.Number = A.Number
GROUP BY B.Number
HAVING COUNT(*) <50) or not exists (SELECT B.Number
FROM Build B
WHERE B.Number = A.Number
) ;

0 Likes

#19

Actually you need a left join instead of an IN or EXISTS

0 Likes

#20

SELECT Time
FROM Showcase A
WHERE A.Number not IN (SELECT B.Number
FROM Build B
WHERE B.Number = A.Number
GROUP BY B.Number
HAVING COUNT(*) >= 50)

0 Likes