How can i count all rows without miss them?

Question

SELECT t1.s_name, count(*) FROM tvSeries AS t1, subTitles AS t2, votes as t3
WHERE 
t1.s_id IN (SELECT t2.s_id WHERE sLang='English') AND 
t1.s_id IN (SELECT t3.s_id WHERE pts=5) AND 
t1.s_id IN (SELECT t3.s_id WHERE uid='britney');

My tvSeries table is like:

s_id      s_cat           s_name
1            comedy          a
2            comedy          b
3            drama           c
4            comedy          d
5            drama           e

My subTitles table is like:

s_id      sLang
1            English
1            Spanish
2            French
2            English
3            English
1            French
4            German
4            English
5            English

My votes table is like:

s_id      uid         pts
1            john        4
1            mia         3
1            britney     5
2            rock        5
3            anna        1
3            britney     5
4            megan       3
5            britney     5

I want to select total number of tvSeries and name of tvSeries in this conditions;

which tvSeries gets 5 star from user 'britney' with English subtitles.

When I use my code, I get only one row with number of tvSeries but i want to see many rows with total value. Can anyone help me?

The published query seems to have nothing to do with the sample data?? — P.Salmon, Jun 25 '20 at 14:47
As mentioned before you have added a sql statement in the beginning about books which does not belong to this question. And if you wanted to get "which tvSeries gets 5 star from user 'britney' with English subtitles." that does not match any rows? britney have only given 5 stars to tvSerie 3 which only have Spanish subtitles, not English? — MrApnea, Jun 25 '20 at 14:56
@MrApnea this table is only a sample, but you are right. When we try sth we cant get any rows. I can try to fix tables now in mins. It seems better now i think. Did it help u? — Jeff Nippards, Jun 25 '20 at 15:07
@P.Salmon oh i see know, i copied false query. Sorry, now i fix it. — Jeff Nippards, Jun 25 '20 at 15:12
Welcome to SO! For future reference, please read over [how to ask](https://stackoverflow.com/help/how-to-ask) and be sure to include a [minimal and complete example](https://stackoverflow.com/help/minimal-reproducible-example). If you make your question easy to answer, you're much more likely to get a good response without unnecessary back-and-forth with clarifying comments. Providing a working example is the easiest way to make your question easy to answer (like I did in my answer using sqlfiddle). — WOUNDEDStevenJones, Jun 25 '20 at 15:36

WOUNDEDStevenJones · Accepted Answer · 2020-06-25T15:32:33.800

0

You can do this with simple JOINs (see this answer for an explanation of JOIN vs ,), and then your conditionals are clean and easy to understand.

SELECT
  t.s_id,
  t.s_name
FROM
  tvSeries t
  JOIN subTitles s ON s.s_id = t.s_id
  JOIN votes v ON v.s_id = t.s_id
WHERE 
  s.sLang = 'English'
  AND v.pts = 5
  AND v.uid = 'britney';

If you want just the count of shows instead, you can do:

SELECT
  COUNT(*) as count
FROM
  ...

You can't easily get both the names of the series as well as the count in the same row (because COUNT is an aggregating function), but if you need it you can do:

SELECT
  GROUP_CONCAT(t.s_name) as series_names,
  COUNT(*) as count
FROM
  ...

though that returns a single row with concatenated series names (a,c,e) rather than rows which are able to be iterated over.

See http://sqlfiddle.com/#!9/2c252c/13 for a working example.

edited Jun 25 '20 at 15:32

answered Jun 25 '20 at 15:27

WOUNDEDStevenJones

5,150
6
41
53

Oh, I understand now why i cant.. Thank you so much. Soo, show my results like

s_name               values
a                             3
b                             3
c                             3

or

s_name               values
a                             4
b                             4
c                             4
d                             4

is impossible? – Jeff Nippards Jun 25 '20 at 15:40

Are you using a server-side language to get these results and/or display them somehow? It's easy to get the count of the number of rows in any server-side language, so you generally want to return all of the data you need (the first query in my answer), and then output the count manually when iterating over the results. It's not _impossible_, but trying to format it like that directly from MySQL is odd. The database should generally return your raw data, and then you can process said data on the server to display it specifically how you'd like. – WOUNDEDStevenJones Jun 25 '20 at 15:50
I'm just trying to improve myself. t was not possible to do everything with server-side languages, I understand that, thank you again :D – Jeff Nippards Jun 25 '20 at 15:57

How can i count all rows without miss them?

1 Answers1