Can someone help me rewrite this query to obtain the results I need?

Discussion in 'MySQL' started by Stacey Lance-shellberg, Aug 24, 2011.

0
  1. #1
    I am looking to get the card id, typecommunication and date created for only those card ids who have at least one of each typecommunication = CMR or Bounced Mail.

    select cardid, typecommunication, datecreated
    from rxc_communications
    where typecommunication in ('Bounced Mail', 'CMR')
    group by cardid
    having count(typecommunication) >1

    This is not working.
     
    Stacey Lance-shellberg, Aug 24, 2011 IP
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    You don't need the having, since the where takes care of that.
     
    Rukbat, Aug 24, 2011 IP
  3. freelanceinphp

    freelanceinphp Member

    Messages:
    134
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    48
    #3
    select cardid, typecommunication, datecreated
    from rxc_communications
    where typecommunication in ('Bounced Mail', 'CMR') and count(typecommunication) >1
    group by cardid
     
    freelanceinphp, Aug 25, 2011 IP
  4. le_punk

    le_punk Peon

    Messages:
    31
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    not sure i understand correctly what you are trying to achieve but...

    select cardid, typecommunication, datecreated
    from rxc_communications
    where cardid in (select cardid from rxc_communications where typecommunication='Bounced Mail')
    and cardid in (select cardid from rxc_communications where typecommunication='CMR')

    dirty and slow but might do (if i understood the problem correctly)
     
    le_punk, Aug 26, 2011 IP