maybe like this: select buyerid from (select buyerid ,sellerid from transation left out join transation on buyerid=sellerid group by buyerid ,sellerid) where sellerid is null
2012/8/14 Prakrati Agrawal <prakrati.agra...@mu-sigma.com> > Dear Phil, > > Can you be a liitle more specific about using the left outer join? > > Thanks and Regards, > Prakrati > > -----Original Message----- > From: Philip Tromans [mailto:philip.j.trom...@gmail.com] > Sent: Tuesday, August 14, 2012 2:55 PM > To: user@hive.apache.org > Subject: Re: NOT IN clause in Hive > > Hive doesn't support IN. You'll need to rewrite your query as a left outer > join, and check whether the RHS is null. > > Phil. > > On 14 August 2012 10:20, Bertrand Dechoux <decho...@gmail.com> wrote: > > According to the error message, you are not using the correct synthax : > > https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select > > #LanguageManualSelect-ALLandDISTINCTClauses > > > > Bertrand > > > > > > On Tue, Aug 14, 2012 at 11:12 AM, Prakrati Agrawal > > <prakrati.agra...@mu-sigma.com> wrote: > >> > >> Dear all, > >> > >> > >> > >> I am trying to execute this query : SELECT distinct(buyerid) from > >> transaction WHERE buyerid NOT IN(SELECT distinct(sellerid) from > >> transaction) > >> > >> This gives me the following error: > >> > >> > >> > >> FAILED: Parse Error: line 1:59 cannot recognize input near 'SELECT' > >> 'distinct' '(' in expression specification > >> > >> > >> > >> How can I rectify the query to get it working? > >> > >> > >> > >> Thanks and Regards, > >> > >> Prakrati > >> > >> > >> ________________________________ > >> This email message may contain proprietary, private and confidential > >> information. The information transmitted is intended only for the > >> person(s) or entities to which it is addressed. Any review, > >> retransmission, dissemination or other use of, or taking of any > >> action in reliance upon, this information by persons or entities > >> other than the intended recipient is prohibited and may be illegal. > >> If you received this in error, please contact the sender and delete the > message from your system. > >> > >> Mu Sigma takes all reasonable steps to ensure that its electronic > >> communications are free from viruses. However, given Internet > >> accessibility, the Company cannot accept liability for any virus > >> introduced by this e-mail or any attachment and you are advised to > >> use up-to-date virus checking software. > > > > > > > > > > -- > > Bertrand Dechoux > > This email message may contain proprietary, private and confidential > information. The information transmitted is intended only for the person(s) > or entities to which it is addressed. Any review, retransmission, > dissemination or other use of, or taking of any action in reliance upon, > this information by persons or entities other than the intended recipient > is prohibited and may be illegal. If you received this in error, please > contact the sender and delete the message from your system. > > Mu Sigma takes all reasonable steps to ensure that its electronic > communications are free from viruses. However, given Internet > accessibility, the Company cannot accept liability for any virus introduced > by this e-mail or any attachment and you are advised to use up-to-date > virus checking software. >
