CODEWITHSUNDEEP
sqlmysqljoin
texai
texai

Reputation: 3736

SQL query to get expected result

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|
+----+

Upvotes: 2

Views: 166

Answers (3)

Isaac A. Nugroho
Isaac A. Nugroho

Reputation: 206

By rephrasing your requirement statement to select table_B which does not have associated table_A with status <> 1. I come to the following query statement:

select distinct b.* 
from table_B b
left outer join table_A_has_B ab on ab.id_B = b.id
left outer join table_A a on a.id = ab.id_A and a.status <> 1
where a.id is null

or

select b.* 
from table_B b
where not exists (select null from table_A_has_B ab 
inner join table_A a on a.id = ab.id_A and a.status <> 1
where ab.id_B = b.id)

Upvotes: 0

Thomas
Thomas

Reputation: 64635

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
                    )

Upvotes: 2

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

EDIT Answer - 

select b.* from table_B b 
left outer join table_A_has_B ab on ab.id_B = b.id
where ab.id in (
    select id from table_A_has_B ab
    where (id_A in (select id from table_A where status = 1 ))
)
or b.id not in 
(select id_B from table_A_Has_B )

OLD answer - 

select b.* from table_B b
left outer join table_A a on b.id = a.id
where a.status = 1 or a.id is null

Upvotes: 3

Related Questions