SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2002
    Location
    Asker, Norway
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select... where id is not in (select... )

    A have a group of people
    Code:
     
    SELECT DISTINCT(p.id), CONCAT_WS(', ',p.etternavn,p.fornavn) AS navn FROM arrlookup al
    LEFT JOIN gruppelookup gl ON al.gr_id = gl.gruppeid
    LEFT JOIN personer p ON gl.personid = p.id
    LEFT JOIN arrangementer a ON al.arr_id = a.id
    WHERE 
    gl.ut_dato > CURDATE() 
    AND al.ut_dato > CURDATE() 
    AND al.arr_id = 22
    ORDER BY navn
    And I want to register attendence (fremmote)
    So, what I want to do is to select those persons in this group (selection) that has not registered attendece
    Code:
     
    WHERE p.id IS NOT IN (SELECT id FROM fremmote WHERE samling_id=11)
    But I get an error.

    I use Mysql 3.23.58.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't use a subquery before version 4.1

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select distinct
           p.id
         , concat_ws(', ',p.etternavn,p.fornavn) as navn 
      from arrlookup al
    left outer 
      join gruppelookup gl 
        on al.gr_id 
         = gl.gruppeid
       and gl.ut_dato > curdate() 
    left outer 
      join arrangementer a 
        on al.arr_id
         = a.id
    left outer
      join personer p 
        on gl.personid 
         = p.id
    left outer
      join fremmote f 
        on p.id     
         = f.id
       and f.samling_id = 11
     where al.ut_dato > curdate() 
       and al.arr_id = 22
       and f.id is null
    order 
        by navn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2002
    Location
    Asker, Norway
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey, thanks

    It works just great


Bookmarks

Posting Permissions

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