Could someone help me understand what alternative do I have for this query?
I am trying to check if a given row exists in the table.

select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
  from page_temp_ext a
where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype b.pagetype);

Hi Mohit,

Hive doesn’t support correlated subqueries.  In this instance, you can do a
left outer join to find values that are not in a table.

SELECT

    “”,

    a.pagename,

    a.pagedetail,

    “”,

    a.pagetitle,

    a.page_id,

    a.pagetype

FROM page_temp_ext a

    LEFT OUTER JOIN page_temp b ON

        a.pagename = b.pagename AND

        a.pagetitle = b.pagetitle AND

        a.page_id = b.pageid AND

        a.pagetype = b.pagetype

WHERE

    b.pagename IS NULL
Hope that helps,

Matt
       On Mon, Oct 1, 2012 at 4:42 PM, Mohit Anchlia <[EMAIL PROTECTED]
> wrote:

> Could someone help me understand what alternative do I have for this
> query? I am trying to check if a given row exists in the table.
>
> select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
>   from page_temp_ext a
> where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
> and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype > b.pagetype);
>

thanks! that works

On Mon, Oct 1, 2012 at 3:43 PM, Matt Tucker <[EMAIL PROTECTED]> wrote:

> Hi Mohit,
>
>
>
> Hive doesn’t support correlated subqueries.  In this instance, you can do
> a left outer join to find values that are not in a table.
>
>
>
>  SELECT
>
>      “”,
>
>      a.pagename,
>
>      a.pagedetail,
>
>      “”,
>
>      a.pagetitle,
>
>      a.page_id,
>
>      a.pagetype
>
>  FROM page_temp_ext a
>
>      LEFT OUTER JOIN page_temp b ON
>
>          a.pagename = b.pagename AND
>
>          a.pagetitle = b.pagetitle AND
>
>          a.page_id = b.pageid AND
>
>          a.pagetype = b.pagetype
>
>  WHERE
>
>      b.pagename IS NULL
>
>
> Hope that helps,
>
> Matt
>
>
>                      On Mon, Oct 1, 2012 at 4:42 PM, Mohit Anchlia <
> [EMAIL PROTECTED]> wrote:
>
>> Could someone help me understand what alternative do I have for this
>> query? I am trying to check if a given row exists in the table.
>>
>> select "",a.pagename,a.pagedetail,"",a.pagetitle,a.page_id,a.pagetype
>>   from page_temp_ext a
>> where 0 = (select count(*) from page_temp b where a.pagename = b.pagename
>> and a.pagetitle = b.pagetitle and a.page_id = b.pageid and a.pagetype >> b.pagetype);
>>
>
>