Thread

1. Antw: LEFT JOIN

   Gerhard Dieringer <dieringg@eba-haus.de> — 2000-07-04T12:46:18Z

   Antti Linno wrote:
   > Greetings.
   >  I have a problem. I have 2 tables. E.g. work and workers. I want to
   > select records, that 1st table has, and the second hasn't(both have id
   > attribute). I mean I can't do it with is NULL, because those records don't
   > exist. I was shown, how it is done with mysql
   
   I think, the following select will solve your problem
   
   select  first.id
   from first
   except
   second.id_first 
   from second;
   
   >
   > select first.id,second.id_first 
   > from first left join second on
   > id=id_first where id_first is NULL;
   > 
   
   This query should never give any result, because you require
   id_first = id and id_first is null
   but if id_first is null then it's  equal to nothing,
   and BTW it's also unequal to nothing.
   
   
   > but when I tried it in psql, it said, not implemented.
   
   Outer joins (left or right joins) are not implemented in PostgreSQL 7.0, but will
   come in 7.1.
   
   > 
   > Antti
   
   Gerhard
   
   
   
   

2. Re: Antw: LEFT JOIN

   Antti Linno <alligator@all.ee> — 2000-07-04T13:34:22Z

   > I think, the following select will solve your problem
   > 
   > select  first.id
   > from first
   > except
   > second.id_first 
   > from second;
   > 
   Nay, I got parse error.
   Antti
   
   
   
   

3. Re: Antw: LEFT JOIN

   Erol Oz <eroloz@bilgi.edu.tr> — 2000-07-04T14:54:03Z

   
   Antti Linno wrote:
   > 
   > > I think, the following select will solve your problem
   > >
   > > select  first.id
   > > from first
   > > except
   > > second.id_first
   > > from second;
   > >
   > Nay, I got parse error.
   
   'select' is missing after 'except'.
   
    select  first.id
    from first
    except
    select second.id_first
    from second;
   
   erol
   
   > Antti
   
   

4. Re: Antw: LEFT JOIN

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-04T19:43:52Z

   Antti Linno <alligator@all.ee> writes:
   >> I think, the following select will solve your problem
   >> 
   >> select  first.id
   >> from first
   >> except
   >> second.id_first 
   >> from second;
   >> 
   > Nay, I got parse error.
   
   Should be ... EXCEPT SELECT second.id_first ...
   
   Anyway, we should have full ANSI join support in 7.2, or 7.1 if
   we get lucky.
   
   			regards, tom lane