How to count records from different tables [closed]

Posted on

Question :

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?

EDIT:

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.

EDIT #2
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.this is the voorstelling table

that is the voorstelling table ^

reserveringen table

that is the reserveringen table ^

form

that’s what the form looks like before hitting filter. ^

form after

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

EDIT #3

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)

Answer :

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'
);

EDIT

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

Leave a Reply

Your email address will not be published.