s.hour >= 343013 AND
communications.hour < 343014
From: Josh Ferguson [mailto:j...@besquared.net]
Sent: Monday, February 16, 2009 10:53 PM
To: hive-user@hadoop.apache.org
Subject: Re: Yet another join issue
The thing I want the WHERE clause to do here is filter the two
Also something Else that seems work is to specify all my conditions
for conversions in the ON clause such as:
LEFT OUTER JOIN
conversions ON
communications.account = conversions.account AND
communications.application = conversions.application AND
The thing I want the WHERE clause to do here is filter the two sets
*before* I join them not after. This is what Prasad's query was doing
unfortunately it wasn't working properly inside of a SELECT TRANSFORM
FROM(...) block. Anyone know the SQL standard involving WHERE clauses
and joins?
I can't but if you do something similar in mysql you'll get what I
expected were the correct results. Not that Mysql is the "standard" by
any means, it was just something I expected to work like that. My
intuition about this is that it would apply the conditions first to
both tables and the
The following will also work. In the case of left outer join, the value of
columns from the missing table will be NULL, so checking whether the value
IS NULL should solve the problem.
SELECT communications.sender_id, conversions.actor_id,
communications.version, conversions.reference_id FROM commu
Will this work if this select statement is already nested in a FROM?
Josh Ferguson
On Feb 16, 2009, at 10:14 PM, Prasad Chakka wrote:
Try below,
SELECT a.sender_id,
b.actor_id,
a.version,
b.reference_id
FROM communications a
LEFT OUTER JOIN (
Try below,
SELECT a.sender_id,
b.actor_id,
a.version,
b.reference_id
FROM communications a
LEFT OUTER JOIN (select conversions.actor_id,
conversions.reference_id from conversions where
conversions.application='reference') b
ON a.re
