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?

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)

closed as unclear what you're asking by Andriy M, hot2use, Marcello Miorelli, McNets, Vérace Mar 27 at 16:56

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Really confusing. Could you try reproducing the problem with two small subsets of the tables involved (with a smaller filter value than 50, obviously) and then post the data samples, the query, the actual result, and the expected result, additionally explaining (just in case) why the result should be like that? When posting the data samples, please consider using DDL/DML statements (CREATE TABLE ..., INSERT INTO ...), as that makes it easier for potential answerers to work on your problem and thus increases the chance of you getting more suggestions. Thank you, and welcome to the site! – Andriy M Mar 27 at 10:37
  • The output of SHOW CREATE TABLE my_table(s)\G; will give the full table definition in a form that we can use. Screenshots are discouraged here - see my profile for some articles on answering questions! I presume that when you say you have fields called "number" and "time" you are translating from the Dutch? You should never have fields that are called after SQL keywords. – Vérace Mar 27 at 14:51
  • If an approximation would suffice, see information_schema.TABLES. – Rick James Apr 16 at 18:22
0

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
  • This still gives me an empty select. I changed the question to show the edit I also tried using UNION which partly worked I'll drop that in the question as an edit as well. – Yvalson Dronkers Mar 24 at 22:11
  • Can you add the full definition of your tables and also supply some example data? This will help me provide a better answer. – Mr.Brownstone Mar 24 at 22:15
  • Just did, I hope that clears things up. But to be clear If your code works then the UNION code is not necessary but since I was trying I thought I'd also show that – Yvalson Dronkers Mar 24 at 22:18
  • it still gave me nothing inside the select. Is there anything you can do with the UNION code that I'm trying to make work as it already shows the Tijd but only from the reserveringen table maybe you know why it's not using the other table information? – Yvalson Dronkers Mar 24 at 22:50
  • Please provide an example of the data that you are working with, the full table definitions and an example of the output that you are looking for. Unless you provide that, I cannot help you further. – Mr.Brownstone Mar 24 at 22:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.