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
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);
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)
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)
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)
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
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
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:
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
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: -
Only time entries from Table1?
Are you only wanting the last time?
If there are 40 entries of number in Table1 and 11 entries in table2 you wanting nothing returned?
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?
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.
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
) ;