Archives of the TeradataForum

Message Posted: Thu, 06 Jul 2006 @ 15:32:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Below is the data from the two tables:

     ---------------------------------------
     Table : vinod_2

     ------------------
     id1    location
     ------------------
     1      Chinchwad
     1      Pimpri
     2      Chinchwad



     Table : location

     --------------
     location_id
     --------------
     Chinchwad
     Pimpri

     ---------------------------------------

I want all those id1 from table vinod_2 where all location_Id's are present in location tables

i.e the output should be id1 2 which i am getting through the below query.

     SELECT id FROM vinod_2
     minus
     (SELECT id
     from
     (select b.id id,a.location_Id,c.id id1
     from location a CROSS JOIN (Select distinct id from vinod_2) b LEFT JOIN
     vinod_2 c ON
     b.id = c.id AND
     a.location_Id = c.location_Id) x
     Where x.id1 IS  NULL);

But i think this can be achieved by much more simpler query any ideas (without using any analytical functions)

Regards,

Vinod

	<Prev	Next>		<<First	<Prev	Next>	Last>>