Thread

1. Re: Antw: Problem with joins

   Jean-Marc Libs <jean-marc.libs@obs.coe.int> — 2000-06-05T13:48:30Z

   On Wed, 5 Jul 2000, Gerhard Dieringer wrote:
   
   > Jean-Marc Libs wrote:
   > >...
   > >I have also tried:
   > >select source_name,data_value from source,data where data_source_id=source_id union select source_name,source_id,NULL from source,data
   > 
   > >This is a bit better, in the sense that I get back all I need, but there
   > >are too many lines: when there is data, I get the line with the data value
   > >and also with NULL.
   > >...
   > 
   > You are on the right way. Change your querry to 
   > 
   > select source_name,data_value 
   > from source,data 
   > where data_source_id=source_id 
   > union 
   > select source_name,source_id
   > from source
   > WHERE source_id NOT IN (SELECT source_id FROM data);
   > 
   > and you will get your expected result.
   > 
   > BTW this simulates an outer join.
   
   Oh, I wasn't aware that outer joins aren't supported :-(
   
   Well, I found that it does kinda work. My app is actually more
   complicated than this, so now I have:
   
   select source_name,data_value 
   from source,data
   where data_source_id=source_id and "conditions on source"
   union
   select source_name,NULL
   from source
   WHERE source_id NOT IN (SELECT data_source_id FROM data where "my conditions on data")
   
   I get exactly the rows I wanted, and I am very glad for the tip (that
   should go in a FAQ, it it isn't already).
   
   But now I wonder where I could put my 'order by source_order' statement,
   as I can't figure out how to put the rows in the proper order :-(
   
   Thanks for the prompt answer anyway,
   Jean-Marc Libs
   
   -- 
   Jean-Marc Libs, ingénieur INTERNET/INTRANET
   Actimage 1 rue St Leon F-67000 STRASBOURG    http://www.actimage.net
   Professionnel : jeanmarc@actimage.fr 
   Lieu de travail : jml@obs.coe.int
   
   
   

2. Antw: Problem with joins

   Gerhard Dieringer <dieringg@eba-haus.de> — 2000-07-05T10:31:16Z

   Jean-Marc Libs wrote:
   >...
   >I have also tried:
   >select source_name,data_value from source,data where data_source_id=source_id union select source_name,source_id,NULL from source,data
   
   >This is a bit better, in the sense that I get back all I need, but there
   >are too many lines: when there is data, I get the line with the data value
   >and also with NULL.
   >...
   
   You are on the right way. Change your querry to 
   
   select source_name,data_value 
   from source,data 
   where data_source_id=source_id 
   union 
   select source_name,source_id
   from source
   WHERE source_id NOT IN (SELECT source_id FROM data);
   
   and you will get your expected result.
   
   BTW this simulates an outer join.
   
   Gerhard