CODEWITHSUNDEEP
mysqlhive
frazman
frazman

Reputation: 33223

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 ')'

Upvotes: 0

Views: 44

Answers (2)

Charif DZ
Charif DZ

Reputation: 14721

Join always in the from section:

SELECT id, 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 day = to_date('some date')

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

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')

;

Upvotes: 2

Related Questions