0

So I currently have this query:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer) 
FROM reserveringen r, voorstelling v 
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer 
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

That query outputs this table:

Voorstellingsnummer  COUNT(r.Voorstellingsnummber)
-------------------  -----------------------------
4                    1
5                    2

The other query is this one:

SELECT v.Tijd, v.Voorstellingsnummer
FROM voorstelling v
WHERE v.Vestigingsnaam = 'Amsterdam' 
AND v.Filmnaam = 'Avengers' 

...which outputs this:

Tijd                 Voorstellingsnummer
-------------------  -------------------
2019-03-24 11:12:00  5
2019-03-24 15:12:00  4

Considerations

  • I need to add a COUNT(v.Voorstellingsnummer) column to the last image the same way as it is in the first one.

  • And then I need to add those COUNTS together to give a totalcount.

  • That total count should be lower than a set number and if it's higher it should not be shown.

When I do this:

SELECT v.Tijd, v.Voorstellingsnummer, COUNT(v.Voorstellingsnummer)
FROM voorstelling v
WHERE v.Vestigingsnaam = 'Amsterdam' 
AND v.Filmnaam = 'Avengers' 

...it returns this:

Tijd,                Voorstellingsnummer, COUNT(v.Voorstellingsnummer)
2019-03-24 11:12:00, 5,                   2

...but instead it should show both the times form the second picture both with a count of 1.

Those counts of 1 should be combined with the other table so the final counts of Voorstellingsnummer=4 should be 2 and Voorstellingsnummer=5 should be 3

Can anyone tell me how to do this?

Further information

Basically if the table reserveringen look has (Voorstellingsnummer = 4) 6x and the table voorstelling has (Voorstellingsnummer = 4) 2x the count outputted by the query should be 8x.

However, currently if (Voorstellingsnummer = 4) appears 3x in the table voorstelling, but has 0 appearances in the table reserveringen it won't be counted.

So what I'm thinking is running this query:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer) 
FROM reserveringen r, voorstelling v 
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer 
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

for the table reserveringen and the table voorstelling. That should result in two tables. The result of the query for table voorstelling should look like this (using number previously mentioned):

Tijd         Voorstellingsnummer   COUN
----------   -------------------   ----
something    5                      1
something    4                      1

..and then the result for the query for table reserveringen should look as follows:

Voorstellingsnummer   COUNT
-------------------   -----
5                      2

That's because table reserveringen has no Tijd column.

Those two tables should then be combined into the following one:

Tijd         Voorstellingsnummer    COUNT
---------    -------------------    -----
something    5                      3
something    4                      1

Basically both Voorstellingsnummer 4/5 approve of the condition Vestigingsnaam='Amsterdam' AND Filmnaam='Avengers'; however only Voorstellingsnummer 5 appears in the reserveringen table. Because of that I need to know how much appearances Voorstellingsnummer = (Some number) has over both tables.

Note: Reserveringen means reservations in dutch, and voorstelling is show. Some shows might have reservations but some might not. Because the show without reservations should still be listed (as it's not full yet).

  • Please post the results as text and not as pictures. There are users who are on networks that block imgur and are unable to load the pictures and unable to help you. – Joe W Mar 25 at 16:08
  • I'm not sure if I can post them as text would just copying the results work? Otherwise it would take me at least one hour to do that by hand – Yvalson Dronkers Mar 25 at 18:07
  • You don't have the ability to export as csv or some other format from your sql tool? The main thing is to have them in a format that isn't just a picture so that more people can see it. – Joe W Mar 25 at 18:10
  • I'm not sure I'm using my schoolserver (I'm a graduate high school student) and we are using phpmyadmin so I think there should be a way to export as CSV but I don't know where – Yvalson Dronkers Mar 25 at 20:31
  • First you JOIN, then you "pivot". – Rick James Apr 18 at 6:30
0

In advance i've never written anything in mysql, but the theory should still be there in case of small syntax errors.

In general i'd advice using INNER JOINS, instead of joining your tables in a WHERE clause, due to easier readability and better coding practice, as it makes it easy to change the sql inner join to an outer join as i've shown below:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)+1 
FROM voorstelling v
INNER JOIN reserveringen r ON r.Voorstellingsnummer = v.Voorstellingsnummer
WHERE v.Vestigingsnaam = 'Amsterdam' 
AND v.Filmnaam = 'Avengers' 
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

I am however very confused as to how you want the counts added? The above query should return what you're asking for. As it sounds like you just want the initial counts to be added with 1. To have this match more function like instead of a hardcoded value, i would have to understand why 4 and 5 should have a count of 1 in the other table?

  • Your code indeed is more clean and does what I want partly. problem is that If I where to remove Voorstellingsnummer = 4 from the reserveringen table it will no longer return that Voorstellingsnummer = 4 as it will no longer be counted. So it's not a simple + 1 but it should add the amount it's counted in the table voorstelling to the amount it's counted in the table reserveringen. I edited my question to give a better explaination – Yvalson Dronkers Mar 26 at 21:32
0

I finally fixed it. I got it working after I edited and everything became clear. This is the query that helped me obtain the desired information:

SELECT Tijd
FROM voorstelling v
WHERE v.Voorstellingsnummer NOT IN
(
SELECT r.Voorstellingsnummer
FROM reserveringen r
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r.Voorstellingsnummer
HAVING COUNT(*) >= 50
)
AND Vestigingsnaam = 'Amsterdam'
AND Filmnaam = 'Avengers';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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