Search code examples
sql-server-2014

SQL - Get results only results from table 1, where all rows on table 2 meet condition true

Ok, so this seems like an easy one but it's got me stumped and I can't find what I'm looking for around the net. It probably is really simple and I've just had a long day and can't get it to work.

I have 2 tables: variant_detail, variant_setting

Sample data:

+--------------------------+-----------------------+
| variant_detail.vad_pd_id | variant_detail.vad_id | 
+--------------------------+-----------------------+
| 3                        | 3                     |
| 18                       | 25                    |
| 3                        | 69                    |
| 5789                     | 8954                  |
| 89                       | 254                   |
| 1880                     | 6987                  |
| 246                      | 879                   |
| 5789                     | 774                   |
+--------------------------+-----------------------+

+----------------------------+------------------------------------------+
| variant_setting.vas_vad_id | variant_setting.vas_discontinued_product |
+----------------------------+------------------------------------------+
| 3                          | TRUE                                     |
| 25                         | TRUE                                     |
| 69                         | FALSE                                    |
| 8954                       | TRUE                                     |
| 254                        | FALSE                                    |
| 6987                       | FALSE                                    |
| 879                        | FALSE                                    |
| 774                        | TRUE                                     |
+----------------------------+------------------------------------------+

What I'm trying to get is only rows vad_pd_id where variant_setting.vas_discontinued_product = TRUE.

This is not a standard join as not all variants will be discontinued. I've tried Distinct and all sorts! E.g. There would be no entry for vad_pd_id = 3 as one row is TRUE and one is FALSE However, there would be an entry for vad_pd_id = 5789 as both rows are TRUE

Here's the code I had a shot with:
Select distinct vad_pd_id From variant_detail Inner Join variant_setting On variant_detail.vad_id = variant_setting.vas_vad_id where variant_setting.vas_discontinued_product = TRUE

Look forward to some assistance from all the techs please!

EDIT

I now need to update a separate table: product_analysis. Field is pa_pd_id
So code something like

update update product_analysis set product_analysis.pa_l_9 =1 *using the select function just created

Solution

  • See bool_and aggregate function (documentation).

    true if all input values are true, otherwise false

    You need to aggregate the vas_discontinued_product values and use bool_and(). So remove WHERE clause:

    where variant_setting.vas_discontinued_product = TRUE

    and add:

    GROUP BY vad_pd_id
HAVING bool_and(variant_setting.vas_discontinued_product)

    And you don't have to add = TRUE for boolean conditions.

    EDIT

    As it turns out that this is no PostgreSQL you can not use bool_and. But there is an alternative method that should work with (I hope) all SQL flavors:

    Cast boolean to int and use min aggregate function. FALSE::int = 0 and TRUE::int = 1, so if min returns 1 that means all values are TRUE.

    GROUP BY vad_pd_id
HAVING min(CAST(variant_setting.vas_discontinued_product AS integer)) = 1