check this need ,, mysql query [closed]

开发者_如何学Python It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

table ab:
partyid          conatctpurpose
1                  general
1                  shipping
1                  phone
1                  fax
2                  general
2                  shipping
2                  phone
3                  phone
3                  fax
3                  mail
4                  GENERAL
5                  fax

every partyid should be assigned mandatory 3 contact purposes ( general,shipping,phone )

partyid can heve other optional contactpurposes

write a query to return partyid which is not assigned the above mandatory set of contact purposes .

---

Though NOT IN is not an optimal query, you can use it if your data set is not very large.

Select partyid from table_ab where contactpurpose NOT IN ('general','shipping','phone');

---

Try this:

SELECT partyid
  FROM ab
WHERE   conatctpurpose IN ('general','shipping','phone')
GROUP BY partyid
HAVING COUNT(DISTINCT conatctpurpose) < 3

---

@above thank you friends for your support . finally i got my solution .

-many taught that this is a homework , noob question etc . -this is a data check report for our project

(select party_id from (SELECT party_id,count(distinct contact_mech_purpose_type_id) as c FROM party_contact_mech_purpose WHERE contact_mech_purpose_type_id in ('BILLING_LOCATION','PAYMENT_LOCATION','SHIPPING_LOCATION','SHIP_ORIG_LOCATION') group by party_id ) t where c<4)

union

(select party_id from party_contact_mech_purpose where party_id not in (SELECT party_id FROM party_contact_mech_purpose WHERE contact_mech_purpose_type_id in ('BILLING_LOCATION','PAYMENT_LOCATION','SHIPPING_LOCATION','SHIP_ORiG_LOCATION') group by party_id ))

order by party_id