Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Arrow Unanswered: Complicated query

    Hi all!!!

    I'm facing problem in getting some info from two mysql tables..

    i hv two tables TAB_A and TAB_B

    TAB_A:

    contact_id(int) | contact_name(varchar)
    --------------------------------------
    1 | XYZ
    2 | ABC


    TAB_B:

    act_id(int) | contact_id(int) | act_status(Enum('C','D')
    ----------------------------------------------------
    1 | 1 | D
    2 | 1 | C
    3 | 2 | C


    Now, i want to select only thos contact name who :
    Do not have any TAB_B.act_status = 'D'

    so that on making query i should get only ABC

    i'm not able to write any query for this.

    Please help.


    Thanks a lot

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select TAB_A.contact_name
      from TAB_A
    inner
      join TAB_B
        on TAB_A.contact_id
         = TAB_B.contact_id
    group
        by TAB_A.contact_name
    having sum(case when TAB_B.act_status = 'D' 
                    then 1 else 0 end)
         = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    2
    HI !!!!


    MANY MANY THANKS....

    THE CODE WORKS.. EXACTLY WHAT I NEEDED..


    THANKS A LOT

    Manish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •