#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    Hi all


    i have a table name called change_user_profiles which contains 3 coloumns namely user_id,profile_key,profile_value.
    there are 6000 user_id's in database but only few id's
    does not contain a row called 'profile.state' in the profile_value coloumn.
    i want to find that rows(which does not contain a 'profile.state' ).
    problem is each user_id has 9 rows and this profile.state is one of them.so if i use the following statement it ll show all id's because it is comparing with each row.
    select distinct user_id from change_user_profiles where profile_key != 'profile.state'


    pls help
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    Hievryone


    small change

    i have a table name called change_user_profiles which contains 3 coloumns namely user_id,profile_key,profile_value.
    there are 6000 user_id's in database but only few id's
    does not contain a row called 'profile.state' in the profile_key coloumn.
    i want to find that rows(which does not contain a 'profile.state' ).
    problem is each user_id has 9 rows and this profile.state is one of them.so if i use the following statement it ll show all id's because it is comparing with each row.
    select distinct user_id from change_user_profiles where profile_key != 'profile.state'


    pls help
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    please try to use a descriptive title, and please don't start multiple threads on the same topic

    try this --
    Code:
    SELECT user_id 
      FROM change_user_profiles 
    GROUP
        BY user_id
    HAVING COUNT(CASE WHEN profile_key = 'profile.state'
                      THEN 'ok'
                      ELSE NULL END ) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo