SQL Help - Retrieving extra rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Help - Retrieving extra rows

Author

Topic

Marge0512
Starting Member

6 Posts

Posted - 2011-11-23 : 15:14:31

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

Select Distinct A.file_name, A.file_desc, A.year, 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 IRS_SDT A, ACTIVITY_LOG B
Where A.File_Name = B.file_name
and A.Downloaded_Date = B.Date_Loaded
order by A.file_name

I need to add another field which is A.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.

The rows with the B.act_code have (I will shorten what is really there since it is identical anyway)

File_name Downloaded_ID Act_CodeMyFileName Cindy L D
MyFileName Cindy L S
MyFileName Jason R D
MyFileName Jason R S

There should only be 2 rows. One with the act_code of D the other with S. What am I doing wrong?

Thanks in advance!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-23 : 15:24:02

Where did you add A.Act_Code?
How is the real statement looking before and after that?

No, you're never too old to Yak'n'Roll if you're too young to die.

Marge0512
Starting Member

6 Posts

Posted - 2011-11-29 : 10:04:25

Hello, and thanks for responding!

I'm sorry, I made a mistake, there is no A.Act_Code, just B.Act_Code and I added it:

Select Distinct A.file_name, A.file_desc, A.year, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_ID,
A.file_size, A.days_to_request, B.File_Name, B.Act_Code, B.Act_Date, B.date_loaded
From IRS_SDT A, ACTIVITY_LOG B
Where A.File_Name = B.file_name
and A.Downloaded_Date = B.Date_Loaded
order by A.file_name

That is what the real statement looks like after.

To clean up what I had before:

File_name Downloaded_ID Act_Code

STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S

The file for "Cindy" should only have an act_code of "D" and the file for "Jason" should only have an act_code of "S".

Marge0512
Starting Member

6 Posts

Posted - 2011-11-30 : 15:05:56

I've been researching and it seems that I should be doing an Inner Join for my Sql. I've changed it but still get the same results. So, I took out selecting fields from the B table and I get the exact number of rows that I need:

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
from IRS_SDT A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
ORDER BY a.downloaded_date

The problem is that I need to select the fields from the B table in order to display them within the table in my application but when I do add them, I get those duplicate rows with the Act_Code. Is there a command I can use to make that field distinct? I've looked over a site that was pretty good in explaining things but I did not find anything for my problem. If anyone can use it here it is:

http://www.w3schools.com/sql/default.asp

I'm starting to think that maybe I need an extra field in my B table that will make the rows more unique. I have all the fields you see listed in my select from the A table and for the B table I have:

File_Name, Act_Code, Act_Date, Activity_ID, and Date_Loaded

File_Name is the primary key for both tables.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-01 : 05:21:29

The file for "Cindy" should only have an act_code of "D" and the file for "Jason" should only have an act_code of "S".
On which condition is to decide if D or S is the wanted act_code???

You should give table structure, sample data and wanted result in relation to the sample data to make it more clear.

No, you're never too old to Yak'n'Roll if you're too young to die.

Marge0512
Starting Member

6 Posts

Posted - 2011-12-02 : 10:23:00

The A table's structure along with sample data
for 1st record of activity:
(I'm sorry it's hard to read. I can't get the
information to neatly separate for the forum).

Name Type
----------------- -------------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60

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

Name Type
---------------- -------------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM

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.

There should only be 2 rows, one with Cindy with an act_code of D and one with Jason with an act_code of S.

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.

We us Oracle 10.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-05 : 08:45:38

To show formatted code use [C0DE] [/C0DE]

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

                                          
 Name                   Type                     
 -----------------     -------------------   
 FILE_NAME              VARCHAR2(50)       STLMK.txt 
 FILE_DESC              VARCHAR2(50)       NON-RESIDENT     
 FILE_LOCATION          VARCHAR2(50)       L:\\NonResFiles
 YEAR                   NUMBER(4)          2008
 LOCATION_DATE          DATE               10/10/2007 
 DOWNLOADED_DATE        DATE               09/04/2008 9:17:00 AM 
 DOWNLOADED_ID          VARCHAR2(50)       Cindy
 FILE_SIZE              CHAR(10)           16212
 DAYS_TO_REQUEST        NUMBER(3)          60


The B table's structure along with sample data
for 1st record of activity:
                                 
Name             Type           
---------------- -------------
FILE_NAME        VARCHAR2(50)     STLMK.txt
ACT_CODE         CHAR(2)          D
ACT_DATE         DATE             10/10/2007 
ACTIVITY_ID      VARCHAR2(50)     downloaded on
DATE_LOADED      DATE             09/04/2008 9:17:00 AM 

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. 

There should only be 2 rows, one with Cindy with an act_code of D and one with Jason with an act_code of S.
 
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.

We us Oracle 10.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-05 : 08:54:21

Now I see: "We us Oracle 10."

Here we are on MS SQL Server so any given solution might not work on your system - sorry.
Better try another forum.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources