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:
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?
tried an answer and this is the code now:
SELECT Tijd FROM voorstelling AS v WHERE Vestigingsnaam = '$geselecteerdvestiging' AND Filmnaam = '$geselecteerdfilmnaam' AND Voorstellingsnummer < ( SELECT COUNT(Voorstellingsnummer) FROM reserveringen WHERE Voorstellingsnummer = v.Voorstellingsnummer)
That is the original code (changed it because the language used in the original code is not english. However the problem might be fixed faster if I show it like it is.) it’s still not working though.
I also tried using UNION like this:
SELECT Tijd FROM voorstelling WHERE Vestigingsnaam = '$geselecteerdvestiging' AND Filmnaam = '$geselecteerdfilmnaam' AND Voorstellingsnummer IN ( SELECT Voorstellingsnummer FROM (SELECT Voorstellingsnummer FROM reserveringen UNION SELECT Voorstellingsnummer FROM voorstelling) x HAVING COUNT(Voorstellingsnummer) < 50)
However this only shows the records of the Table named reserveringen and doesn’t take into account that there are more records of Voorstellingnummer in the table voorstelling. So it only counts the records of how many time Voorstellingnummer appears in the reserveringen table while there are a bunch more inside voorstelling.
if someone could tell me why that is it could fix things too maybe.
I didn’t exactly know what you mean with full table definition so I’m just providing as much information as possible here u go:
| voorstelling | |
| | Voorstellingsnummer |
| | 1 |
| | 1 |
| | 2 |
| reserveringen | |
| | Voorstellingsnummer |
| | 3 |
| | 4 |
I need the select of my form to show all the voorstellingnummers as long as they arent counted more than 50 times. but currently it’s only showing them from the reserveringen table. So it needs to count the ones from the voorstelling table as well.
that is the voorstelling table ^
that is the reserveringen table ^
that’s what the form looks like before hitting filter. ^
and after hitting filter. however it should also show the time 15:12:00 but is only showing 11:12:00 thats the problem hope this helps
I made some more changes to the code and it’s now showing the content in select without using UNION the problem still holds though where the count from the subquery doesn’t take the records from the other table.
the code looks like this now:
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)
You aren’t actually referring to the outer number and this is why you can’t get the results that you need. What you should do is use a Correlated Subquery to return the results that you want. This will filter the count by the referencing number, like so:
SELECT `Time` FROM `Showcase` AS s WHERE `Number` < ( SELECT COUNT(`Number`) FROM `Build` WHERE `Number` = s.`Number' );
Based on the information that you have provided, you can combine the two approaches to achieve what you want:
SELECT Tijd FROM voorstelling AS v WHERE Vestigingsnaam = '$geselecteerdvestiging' AND Filmnaam = '$geselecteerdfilmnaam' AND Voorstellingsnummer < ( SELECT SUM(Voorstellingsnummer) FROM (SELECT COUNT(Voorstellingsnummer) AS Voorstellingsnummer FROM reserveringen WHERE Voorstellingsnummer = v.Voorstellingsnummer UNION ALL SELECT COUNT(Voorstellingsnummer) FROM voorstelling WHERE Voorstellingsnummer = v. Voorstellingsnummer)) x