How to make multiple where clause in SQL joins-CodePudding

I have two tables colorcast_app_show and colorcast_app_content.

I'm using postgreSQL database.

I want to get all shows data from show table and from content table get the count of the content data but draft=false.

colorcast_app_show table Data:

 id | show_name | description | host_name | host_social_account_link |            thumbnail_image             | publish_date | status |            created_on            | user_id
---- ----------- ------------- ----------- -------------------------- ---------------------------------------- -------------- -------- ---------------------------------- ---------
  1 | Test1     | nil         | nil       | nil                      | media/download_6W1VYGZ.jpg             | 2022-01-04   | f      | 2022-01-04 16:34:55.84046 05:30  |       1
  2 | ttt       | desc        | name      | link                     | media/MicrosoftTeams-image_UMTq0YY.png | 2022-01-19   | f      | 2022-01-05 17:12:39.4206 05:30   |       2
  7 | show 2    | desc        | name      | link                     | media/MicrosoftTeams-image_WLoXCfp.png | 2022-01-12   | f      | 2022-01-13 00:49:04.345571 05:30 |       2
 10 | sdsds     | sdsds       | dssds     | link                     | media/MicrosoftTeams-image_O4BrUjO.png | 2022-01-13   | f      | 2022-01-13 12:02:34.404602 05:30 |       2
 13 | show 1    | desc        | name      | link                     | media/signin_Y68p1uT.jpg               | 2022-01-14   | f      | 2022-01-13 13:43:34.328246 05:30 |      60
 14 | show 2    | njnjsnd     | jjj       | kjkjk                    | media/download_EXIHERo.jpg             | 2022-01-13   | f      | 2022-01-13 14:37:27.999332 05:30 |      60
 15 | show 3    | desc        | name      | link                     | media/MicrosoftTeams-image_nEojgxZ.png | 2022-01-13   | f      | 2022-01-13 17:13:22.050878 05:30 |      60

colorcast_app_content table Data:

 id | content_type | sponsor_link | status |             added_on             |            content_file            | title  | subtitle | description | publish_now | schedule_release | expiration | show_id | user_id | draft
---- -------------- -------------- -------- ---------------------------------- ------------------------------------ -------- ---------- ------------- ------------- ------------------ ------------ --------- --------- -------
 46 | Lecture      | links        | f      | 2022-01-13 13:43:52.959811 05:30 | media/ADP_ver3_-_Copy_Pb8hwPq.xlsx | Title1 | dssd     | ddsds       | Now         | 2022-01-13       | 2022-01-12 |      13 |      60 | f
 48 | Lecture      | dsdsds       | f      | 2022-01-13 14:46:20.242004 05:30 | media/Dancing_Script_GTlu8Cx.zip   | sdsds  | sdsds    | ssdsds      | Later       | 2022-01-13       | 2022-01-27 |      14 |      60 | t
 47 | Seminar      | sss          | f      | 2022-01-13 14:43:32.773371 05:30 | media/Dancing_Script_TU5JCBx.zip   | sdsdsd | sfsfs    | kjkjkjk     | Later       | 2022-01-13       | 2022-01-20 |      15 |      60 | t
 45 | Lecture      | link         | f      | 2022-01-13 12:58:19.745322 05:30 | media/Dancing_Script_Bn1DtxG.zip   | Title1 | nullllll | nsss        | Later       | 2022-01-13       | 2022-01-19 |       7 |       2 | f

I have used below SQL join:

SELECT cas.show_name, COUNT(cac.show_id), cas.status, to_char("created_on", 'DD/MM/YYYY'), to_char("publish_date", 'DD/MM/YYYY'), cas.id FROM colorcast_app_show as cas LEFT JOIN colorcast_app_content as cac ON cac.show_id = cas.id where cas.user_id=60 GROUP BY cas.id, cas.show_name;

And got the below output

 show_name | count | status |  to_char   |  to_char   | id
----------- ------- -------- ------------ ------------ ----
 show 1    |     1 | f      | 13/01/2022 | 14/01/2022 | 13
 show 2    |     1 | f      | 13/01/2022 | 13/01/2022 | 14
 show 3    |     1 | f      | 13/01/2022 | 13/01/2022 | 15

I want the output as below:

 show_name | count | status |  to_char   |  to_char   | id
----------- ------- -------- ------------ ------------ ----
 show 1    |     1 | f      | 13/01/2022 | 14/01/2022 | 13
 show 2    |     0 | f      | 13/01/2022 | 13/01/2022 | 14
 show 3    |     0 | f      | 13/01/2022 | 13/01/2022 | 15

As above output show 2 & show 3 content saved in draft so i don't want to count that data.

Please let me know if any one have solution.

Thanks in advance.

CodePudding user response：

SELECT cas.show_name, 
       COUNT(cac.show_id), 
       cas.status, 
       to_char("created_on", 'DD/MM/YYYY'), 
       to_char("publish_date", 'DD/MM/YYYY'), 
       cas.id
FROM   colorcast_app_show as cas 
       LEFT OUTER JOIN colorcast_app_content as cac 
          ON cac.show_id = cas.id 
             AND cac.draft = false
WHERE  cas.user_id=60 
GROUP  BY cas.id, cas.show_name;

CodePudding user response：

In your SELECT you can change the COUNT to a SUM and add a CASE on the inside of the function to do something like the following:

SELECT cas.show_name,
SUM(CASE WHEN cac.publish_now = 'Now' THEN 1 ELSE 0 END), 
     cas.status, 
     to_char("created_on", 'DD/MM/YYYY'),
     to_char("publish_date", 'DD/MM/YYYY'),
     cas.id 
FROM colorcast_app_show as cas 
LEFT JOIN colorcast_app_content as cac 
ON cac.show_id = cas.id where cas.user_id=60 
GROUP BY cas.id, cas.show_name;

This will specify that you want to add one to the "count" (technically a sum, but it has the same effect) only when it is in a published/publishable state.