>Hi, Dmitry, I realize this specific thread is a few weeks old, and I believe you got your answer, but just to emphasize something....(and I learned this from Sergey years ago)
>
>
>select Table1.USER_NAME as USER_1, MAX(USER_EMAIL) as USER_1_EMAIL, MAX(DATE_IN) AS USER1_LASTDATE_IN FROM Table1
>LEFT JOIN Table3 on Table1.USER_NAME = Table3.USER_NAME GROUP BY Table1.USER_NAME
>LEFT JOIN Table2 ON Table2.USER_PK = Table1.USER_PK
>
>
>In most (maybe even all?) implementations of SQL, any time you issue a SQL SELECT against a pattern of multiple 1-many relationships from the same parent table, you MUST use a subquery of some type. Best of my knowledge, any time you issue a straight SELECT against multiple 1-many relationships , you'll wind up getting incorrect/over-aggregated results. When I taught SQL I used the following scenario...seemingly a very simple one....to show when subqueries are required.
>
>If you join Table A (a master table of jobs) to Table B (a table of employee hours per job).....and let's say that Job XYZ has 4 timesheet rows in Table B.
>And then you join Table A to Table C ( a list of purchase records per job), and let's say that Job XYZ has 3 purchase rows in Table C ...
>
>If you join Table A to Table B and then Table A to Table C in the same SELECT, here's what will happen....when you try to do it all at once.
>
>The "internal product" of the join between A and B will produce 4 instances of Job XYZ....(for each of the four timesheet rows)
>The "internal product" of the join between A and C will produce 3 instances of Job XYZ.....for each of the 3 timesheet rows
>
>When A is Joined to C.....the four instances of the product from A to B will be used...and so the sum of your purchase amount will be 4X higher.
>When A is joined to B.....the three instances of the produce from A to C will be used....and the sum of your timesheet rows will be 3X higher.
>
>So, you're not "just" joining A to C....you're joining on "whatever got joined to A from other JOINs" to C
>And you're not "just" joining A to B....you're joining on "whatever got joined to A from other JOINs" to B.
>
>It's not a bug, it's one of those situations where SQL is doing exactly what we are telling it to do, not what we "want" it to do.
>
>So again, any time you have a master table being joined to multiple 1-many tables, you must use a subquery to break it down, to avoid this pattern.
>
>Hope that helps....
Thank you, Kevin.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham