Multiple rows of the same thing

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

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple rows of the same thing

Author

Topic

uneek78
Starting Member

13 Posts

Posted - 2012-12-10 : 13:10:20

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 13:13:35

You are doing cross join over and obviously duplicate records. What are joining condition?

uneek78
Starting Member

13 Posts

Posted - 2012-12-10 : 14:18:14

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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 14:24:51

You need to relate these tables (Account, ReportEvent, ReportEventType, Patient, ExplorerSnapshot) by joining.

uneek78
Starting Member

13 Posts

Posted - 2012-12-10 : 14:28:27

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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 14:32:52

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'

uneek78
Starting Member

13 Posts

Posted - 2012-12-10 : 14:47:13

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'

uneek78
Starting Member

13 Posts

Posted - 2012-12-10 : 14:48:06

Nevermind, it didn't underline. Evidently this stuff is a little over my head.

uneek78
Starting Member

13 Posts

Posted - 2012-12-10 : 15:16:34

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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 15:29:36

Those are joining condition between related tables.

Can you post DDL structure of your tables so people can help you with?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-12-10 : 19:21:44

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources