SQL Server Performance

Problem with Select Statement

Discussion in 'General Developer Questions' started by josephmp, Apr 26, 2006.

  1. josephmp New Member

    I have two Tables Table1 and Table2 having values

    Table1
    --------------------
    A B
    ------
    1 a
    2 b
    3 c
    4 d
    5 e

    Table2
    --------------------
    X Y Z
    ------------
    1 a 1
    2 a 0
    3 a 1
    4 b 1
    5 b 1
    6 c 0
    7 c 0
    8 d 1
    9 e 0


    Table1 has Primary Column as A and Table2 X
    Table2 is the description table of the column B of Table1
    Table1 and Table 2 are linked using the column B and Y
    Table2 has a flag Field Z (1/0)

    My need is to find those rows in Table1 which has all the values of its corresponding column Z in Table2 as 1

    My query return those columns which have any of the Z fields of Table2 as 1

    My query is

    select Table1.A from Table1, Table2 where
    Table1.B = Table2.Y
    and Table2.Z = 1 group by Table1.A

    My Query Returns answer as 1, 2, 4

    whereas i need the answer as 2 and 4
    ie not showing 1 which has a 0 value in the 2nd row of Table2

    Please Help
  2. mmarovic Active Member

    select a.A
    from table1 a
    where exists(select *
    from table2 b
    where b.y = a.b
    and b.z = 1)
    and not exists(select *
    from table2 c
    where c.y = a.b
    and c.z = 0)
  3. Adriaan New Member

    You need to do a 'negative lookup' here, check the NOT EXISTS subquery ...

    SELECT DISTINCT T1.A
    FROM dbo.Table1 T1
    INNER JOIN dbo.tbl2 T2 ON T1.B = T2.Y
    WHERE T2.Z = 1
    AND NOT EXISTS (SELECT * FROM dbo.tbl2 Tx WHERE Tx.Y = T2.Y AND Tx.Z = 0)

Share This Page