Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Pakistan, Karachi
    Posts
    22

    Unhappy Unanswered: display records that do not exist in another table

    hi all,

    I have following tables and respective fields:
    models(model_code,display_name);
    another table
    group_models(group_code,model_code);

    I want to display those models that do not exist in the current group.. the group_code is the code of the group..

    I have tried following query but it does not return anything from it... my query is

    select distinct a.model_code, a.display_name from models a,group_models b where b.model_code<>a.model_code and b.group_code='0012' order by a.display_name

    I also tried following nested query it says that I have an error in the sql syntax :

    select distinct model_code, display_name
    from models
    where model_code not in (select distinct model_code from group_models where group_code='0012')


    Will be waiting for an urgent response... as this really is an urgent matter

    Regards
    Shani

  2. #2
    Join Date
    Jul 2004
    Location
    Dundee, Scotland
    Posts
    107
    select distinct a.model_code, a.display_name from models a,group_models b where b.model_code=a.model_code and b.group_code!='0012' order by a.display_name

    re the nested query, which version of MySQL are you using?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select a.model_code
         , a.display_name 
      from models a
    left outer
      join group_models b 
        on a.model_code 
         = b.model_code
       and b.group_code = '0012' 
     where b.model_code is null
    order 
        by a.display_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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