CODEWITHSUNDEEP
mysqlsql
Ki Ko
Ki Ko

Reputation: 322

divided sql in condition WHERE

I have 3 tables:

Table A: id, name

Table B: id, name, date(Datetime)

Table C: idA, idB, flag(boolean)

For current I have a SQL:

SELECT A.* from A
WHERE (
    A.name LIKE '%str%'
    AND NOT (
        A.id IN (
            SELECT C.idA
            FROM C 
            INNER JOIN B 
            ON ( C.idB = B.id ) 
            WHERE B.name LIKE '%str2%' 
                  AND C.flag = True 
                  AND B.date >= '2016-10-20'
        )
    )
)

I want to divided condition WHERE same:

SELECT A.* from A
WHERE (
    A.name LIKE '%str%'
    AND NOT (
        A.id IN (
            SELECT C.idA
            FROM C 
            INNER JOIN B 
            ON ( C.idB = B.id ) 
            WHERE B.name LIKE '%str2%'
        )
        AND A.id IN(
            SELECT C.idA
            FROM C
            WHERE C.flag = True
        )
        AND A.id IN(
            SELECT C.idA
            FROM C 
            INNER JOIN B 
            ON ( C.idB = B.id ) 
            WHERE B.date >= '2016-10-20'
        )
    )
)

But it's not working fine. Please help me to divided condition. Thank you!

Upvotes: 1

Views: 93

Answers (2)

Hai Duong
Hai Duong

Reputation: 31

This query will be return same result with your first query:

SELECT A.* from A
WHERE (
    A.name LIKE '%str%'
    AND NOT (
        A.id IN (
            SELECT C.idA from C
            where C.id in(
                SELECT C.id from C
                WHERE (
                    C.id IN (
                        SELECT C.id
                        FROM C 
                        INNER JOIN B 
                        ON ( C.idB = B.id ) 
                        WHERE B.name LIKE '%str2%'
                    )
                    AND C.id IN(
                        SELECT C.id
                        FROM C
                        WHERE C.flag = True
                    )
                    AND A.id IN(
                        SELECT C.idA
                        FROM C 
                        INNER JOIN B 
                        ON ( C.idB = B.id ) 
                        WHERE B.date >= '2016-10-20'
                    )
                )
            )
        )
    )
)

Upvotes: 0

GrApDev
GrApDev

Reputation: 150

SELECT A.* from A
WHERE (A.name LIKE '%str%')
    AND 
    (NOT (A.id IN (SELECT C.idA FROM C INNER JOIN B ON (C.idB = B.id) WHERE B.name LIKE '%str2%')))
    AND 
    (A.id IN (SELECT C.idA FROM C WHERE C.flag = True))
    AND 
    (A.id IN (SELECT C.idA FROM C  INNER JOIN B  ON (C.idB = B.id) WHERE B.date >= '2016-10-20'))

Upvotes: 1

Related Questions