SQL query to get expected result

Question

This is my DB schema

Table_A
-id 
-status

Table_B
-id

Table_A_has_B
-id
-id_A
-id_B

I need to select all records from Table_B, where all associated Table_A records has status=1 and if a Table_B record hasn't any associated Table_A also should be selected.

Test case:

CREATE TABLE table_a (id int(2),status int(1));
INSERT INTO table_a (id, status)
VALUES (1,1),(2,0),(3,1),(4,1),(5,1);

CREATE TABLE table_b (id int(2));
INSERT INTO table_b (id) VALUES (1),(2),(3),(4);

CREATE TABLE table_a_has_b (id int(2),id_A int(2),id_B int(2));
INSERT INTO table_a_has_b (id, id_A, id_B)
VALUES(1, 1, 1),(2, 2, 1),(3, 3, 1),(4, 4, 2),(5, 5, 2),(6, 3, 4),(7, 4, 4);

The query should select:

+----+
|b.id|
+----+
|   2|
|   3|
|   4|
+----+

Id 1 shouldn't be selected because one of its table_a records has status=0
Id 2 and 4 are should be selected because all its table_a records has status=1
Id 3 should be selected because don't has table_a records associated, another point of view for the same criterion is: Id 3 should be selected because don't have any table_a records where status=0

First, there is no entry in the middle table for b_id = 3 so why does it show in your output? Second, there is an entry for b_id = 1 and it is associated with a Table_A row where status = 1 so why does it not show in your output? — Thomas
@Thomas: I just added the explanation about resultset expected — texai
Ok. Only now does it make sense what you are trying achieve. I've updated my answer that provides a solution to your puzzle. — Thomas

Thomas Thomas · Accepted Answer · 2011-02-18T17:40:13

Select ...
From Table_B As B
    Left Join   (
                Select AB.id_B, A.id, A.status
                From Table_A_has_B As AB
                    Join Table_A As A
                        On A.id = AB.id_A
                Where A.status = 1
                ) As Z
        On Z.id_B = B.id

This portion of the original post is not clear: if a Table_B record hasn't any associated Table_A also should be selected..

If you only want rows from Table_B then:

Select B.*
From Table_B As B
Where Exists    (
                Select 1
                From Table_A_has_B As AB
                    Join Table_A As A
                        On A.id = AB.id_A
                Where A.status = 1
                    And AB.id_B = B.id
                )

If you want rows from Table_ B where either there are no rows in Table_A_has_B for a given id_B or where if there are rows it must be associated with a Table_A item where status is 1, then :

Select B.*
From Table_B As B
Where Exists    (
                Select 1
                From Table_A_has_B As AB
                    Join Table_A As A
                        On A.id = AB.id_A
                Where A.status = 1
                    And AB.id_B = B.id
                )
    Or Not Exists   (
                    Select 1
                    From Table_A_has_B As AB
                    Where AB.id_B = B.id
                    )

If what you want is rows from Table_A but only where the status is 1 and for all others a null, then the first query I provided would be the solution. Providing us with some expected output results would obviously help tremendously.

Edit given update to OP

Given your update, which only now makes it clear what you are actually trying to achieve, you do the following:

Select B.id
From Table_B As B
Where Not Exists    (
                    Select 1
                    From Table_A_has_B As AB
                        Join Table_A As A
                            On A.id = AB.id_A
                    Where A.status <> 1
                        And AB.id_B = B.id
                    )

SQL query to get expected result

3 Answers