SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Multiple rows of the same thing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

uneek78
Starting Member

13 Posts

Posted - 12/10/2012 :  13:10:20  Show Profile  Reply with Quote
When I run the following SQL Query it gives me the information I want, but it turns up MULTIPLE rows of the same thing over and over. So I'll get 20 of the same thing and then for the next result I'll get 20 of the same thing and so on and so on. So the report ends up being miles long. Instead of 1 line of information and then the next line of information. Each line of information is duplicated A LOT!

SELECT Account.LoginName, Account.LastModifiedDate, ReportEvent.EventTime, ReportEventType.Name, Patient.MRN, ExplorerSnapshot.OrderID, ExplorerSnapshot.Accession

FROM Account, ReportEvent, ReportEventType, Patient, ExplorerSnapshot
WHERE ReportEventType.Name = 'Delete' AND Account.LoginName = 'alexa007'

;

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  13:13:35  Show Profile  Reply with Quote
You are doing cross join over and obviously duplicate records. What are joining condition?
Go to Top of Page

uneek78
Starting Member

13 Posts

Posted - 12/10/2012 :  14:18:14  Show Profile  Reply with Quote
I'm sorry. How can I pull the data from the tables w/o it "cross joining"? I just want a report for each time the user in question has deleted a report from our system.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  14:24:51  Show Profile  Reply with Quote
You need to relate these tables (Account, ReportEvent, ReportEventType, Patient, ExplorerSnapshot) by joining.

Go to Top of Page

uneek78
Starting Member

13 Posts

Posted - 12/10/2012 :  14:28:27  Show Profile  Reply with Quote
Would joining them make a change in the database? Or is it just a query that wouldn't change anything? Because I have no intentions of changing anything in the database.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  14:32:52  Show Profile  Reply with Quote
No I mean Queries like this

SELECT Account.LoginName, Account.LastModifiedDate, ReportEvent.EventTime, ReportEventType.Name, Patient.MRN, ExplorerSnapshot.OrderID, ExplorerSnapshot.Accession
FROM Account A
inner join ReportEvent RP on RP.<FK> = A.<PK>
inner join ReportEventType RE on RE.<PK> = RP.<FK> 
inner join Patient......
inner join ExplorerSnapshot.......
WHERE ReportEventType.Name = 'Delete' AND Account.LoginName = 'alexa007'
Go to Top of Page

uneek78
Starting Member

13 Posts

Posted - 12/10/2012 :  14:47:13  Show Profile  Reply with Quote
So I'm a little new to this. I have no idea what the things that I have underlined are:

SELECT Account.LoginName, Account.LastModifiedDate, ReportEvent.EventTime, ReportEventType.Name, Patient.MRN, ExplorerSnapshot.OrderID, ExplorerSnapshot.Accession
FROM Account A
inner join ReportEvent RP on RP.<FK> = A.<PK>
inner join ReportEventType RE on RE.<PK> = RP.<FK>
inner join Patient......
inner join ExplorerSnapshot.......
WHERE ReportEventType.Name = 'Delete' AND Account.LoginName = 'alexa007'
Go to Top of Page

uneek78
Starting Member

13 Posts

Posted - 12/10/2012 :  14:48:06  Show Profile  Reply with Quote
Nevermind, it didn't underline. Evidently this stuff is a little over my head.
Go to Top of Page

uneek78
Starting Member

13 Posts

Posted - 12/10/2012 :  15:16:34  Show Profile  Reply with Quote
I'm googling to death over here. Nothing explains the JOIN thing very well. I've seen it explained, but none of the articles I come across seem to match my situation. The articles are very simple. I want to pull from all those tables, but I just don't want the information duplicating if it's the same. I'm sorry I don't know what all that

RP on RP.<FK> = A.<PK>

and the following stuff that looks like it came from. I don't know what any of the stuff stands for either.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  15:29:36  Show Profile  Reply with Quote
Those are joining condition between related tables.

Can you post DDL structure of your tables so people can help you with?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 12/10/2012 :  19:21:44  Show Profile  Reply with Quote
For a given row in Account, how do you know which rows in ReportEvent (or any of the other tables) "belong" (or relate) to that row? i.e. what are the common columns in both tables?
You need to answer that question for all tables then you have your joins. Note that you may not have direct relations - it's fine to go Account->ReportEvent->ReportEventType for example.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000