Fraz Fraz - 4 months ago 11
SQL Question

generating a query to join distinct records

A newbie in sql/ hive...

SELECT id,seed, day FROM table_1 WHERE day = to_date('2016-06-09') limit 5;
302766500 R388899 2016-06-09
692010468 R61140 2016-06-09
662084962 R165803 2016-06-09
1818260515 R411276 2016-06-09
646246322 R426737 2016-06-09


SELECT id, exp, day FROM table_2 WHERE day = to_date('2016-06-09') limit 5;
OK
2595 e137_1 2016-06-09
2595 e137_2 2016-06-09
4372 e137_1 2016-06-09
7256 e137_1 2016-06-09
18674 e137_1 2016-06-09
Time taken: 1.475 seconds, Fetched: 5 row(s)


As you can see in table 2.. listener id is repeated (2595)..

So, from table 2, I want to find out the distinct ids between two day ranges

and then find the seed of those ids from table 1 between a given date range..

SELECT id, seed FROM table_1 WHERE day = to_date('some date')
JOIN (
SELECT DISTINCT id FROM table_2 WHERE day = to_date('some_date')
) l
ON l.id = id;


But i get this error:

ParseException line 3:0 missing EOF at 'JOIN' near ')'

Answer

seems the where is in wrong position

SELECT table_1.id, table_1.seed FROM table_1 
JOIN (
SELECT DISTINCT id FROM table_2 WHERE day = to_date('some_date')
) l
ON l.id = table_1.id
WHERE table_1.day = to_date('some date')

;