Hello, I have this SQL that returns the correct amount of rows which should be
2:

Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded

I need to add another field in the Select query which is B.Act_Code. When I do,
I get 2 extra rows. I do not know how to make these rows distinct.

The A table's structure is along with sample
data for 1st record:




The B table's structure is along with sample
data for 1st record:

The second record of activity would all be the same except Cindy would be
"Jason", act_code would be an "S", activity_id would be "sent on" and then of
course the dates would be changed to whenever the new information was saved
within the system.

I am getting something like this (shortened of course):



There should only be one row for Cindy with a D act_code and one row for Jason
with an S act_code. For some reason, Cindy and Jason each get a row with the
different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code
in the SQL statement.

Cindy should have the D Act_Code because she downloaded that file name and Jason
should have the S because he sent that file to someone else. Every time a file's
activity changes, it is entered into the system so we can keep track of where
the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason
should have the S because he sent that file to someone else. Every time a file's
activity changes, it is entered into the system so we can keep track of where
the files are.

Also, I get the 2 extra rows when I add activity_id field to the select.

I use Oracle 10.

What am I doing wrong?

Thanks in advance!!

Hi Marge,

>> I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.


That' because you use a "delect distinct" that eliminates duplicate rows.

*********************************************

In a standard "equi" join, you get ONLY the number of rows that match:



See Here:

http://dba-oracle.com/googlesearchsite_pro...mp;q=outer+join

If you want to learn it fast, try the Easy Oracle SQL book:

http://www.rampant-books.com/menu_six_packs_bundles.htm#easy

Good Luck!

Thanks so much for this info!!