One to many table - record selection

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 2000 Forums

Transact-SQL (2000)

One to many table - record selection

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-15 : 04:31:22

Here's the sql from my View. If there are 3 records in the many table it lists tblFileRequests.MovementId 3 times, but I only want it list once. Basically all I want to do is list records from tblFileRequests IF there is a record in tblFileRequestDetails.

SELECT TOP 100 PERCENT *
FROM dbo.tblFileRequests LEFT OUTER JOIN
dbo.tblFileRequestDetails ON dbo.tblFileRequests.MovementId = dbo.tblFileRequestDetails.MovementId
WHERE (dbo.tblFileRequests.Submitted = 1) AND (dbo.tblFileRequests.Completed = 0) AND (dbo.tblFileRequestDetails.MovementId > 0)
ORDER BY dbo.tblFileRequestDetails.MovementId DESC

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-15 : 05:01:21

[code]SELECT *
FROM dbo.tblFileRequests R
where Exists (select * from dbo.tblFileRequestDetails rd where rd.MovementId = r.MovementId)
and Submitted = 1 AND Completed = 0
ORDER BY MovementId DESC[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-15 : 05:25:56

Thanks, that works fine. What is 'rd' and R ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-15 : 05:30:09

They are called aliases. They are used to avoid repeating lengthy table names again and again.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-15 : 05:49:06

Ah, I can that now - thanks. I am self taught so am learning all the time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 06:15:53

In sql server help file read about aliases, table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources