Complicated query

Manish Yadav · #1 (**permalink**) 08-23-04, 06:44

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

r937 · #2 (**permalink**) 08-23-04, 07:17

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

Manish Yadav · #3 (**permalink**) 08-23-04, 07:57

HI !!!!

MANY MANY THANKS....

THE CODE WORKS.. EXACTLY WHAT I NEEDED..

THANKS A LOT

Manish

Internet Services

Web Development

Digital Marketing

Consumer Tech