Fetching all the rows frm 2 tables based on a FK.

Question

Post reply

Fetching all the rows frm 2 tables based on a FK.

Snigdha Sinha

SSC-Addicted

Points: 470
More actions
October 10, 2005 at 8:34 am

#87972

Hi,
I have a stored procedure which reads all the contents of a table to display it in a datagrid.
CREATE PROCEDURE sproc_getTestRequest
AS
SELECT [TestRequestNo],
[TestFolderNo],
[TestRequestObjective],
[TestRequestDescription],
[RequestType],
[TestRequestSentDate],
[TestRequestReceivedDate],
[ExpectedPartsReceivedDate],
[RequestedStartDate],
[RequestedCompletionDate]
FROM TestRequest
Now, for every record in this table, I want to fetch a record from a Table called Project.
The field TestRequestNo is a PK in TestRequest table and FK in Project table.
So I want to get the records from the Project table based on this key. So that when I display
the data grid, all the TestRequest records with their respective Projects are displayed
together.
My problem is, when I state a query with the Where clause like this:
WHERE Project.TestRequestNo = TestRequest.TestRequestNo
it ony returns one record and not all the records.
Wheres I want all the records returned at one time.
Please guide me with this.
Thanks,
Snigdha

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

It sounds to me like you need something like this:

SELECT tr.[TestRequestNo],

tr.[TestFolderNo],

tr.[TestRequestObjective],

tr.[TestRequestDescription],

tr.[RequestType],

tr.[TestRequestSentDate],

tr.[TestRequestReceivedDate],

tr.[ExpectedPartsReceivedDate],

tr.[RequestedStartDate],

tr.[RequestedCompletionDate], p.*

FROM TestRequest tr left join Project P on tr.TestRequestNo = P.TestRequestNo

The left join ensures that records are returned from TestRequestNo, even if there is no matching record in Project.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Snigdha Sinha SSC-Addicted Points: 470 More actions · Answer 2

Hi,

Let me explain a little further...

The point is that there are matching records.

The Project table has a list of Projects and each TestRequest in the TestRequest table is asociated with a Project.

So the ProjectId, PK of Project table is a FK in TestRequest.

Now I want the output in a way that...the query should return...

all the TestRequests and for each TestRequest it should return the Project it is associated with. So the result should be the following columns:

TestRequestNo, TestFolderNo,..etc fields of TestRequest,ProjectId of that TestRequest

I hope I did clarify it now

Snigdha

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

OK - I think I was on the right track except for the JOIN:

CREATE PROCEDURE sproc_getTestRequest

AS SELECT tr.[TestRequestNo],

tr.[TestFolderNo],

tr.[TestRequestObjective],

tr.[TestRequestDescription],

tr.[RequestType],

tr.[TestRequestSentDate],

tr.[TestRequestReceivedDate],

tr.[ExpectedPartsReceivedDate],

tr.[RequestedStartDate],

tr.[RequestedCompletionDate], p.*

FROM TestRequest tr left join Project P on tr.ProjectID = P.ProjectID

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Snigdha Sinha SSC-Addicted Points: 470 More actions · Answer 4

Hi,

This was right on target !!!!

The output is exactly in the way I wanted it.

Thanks a lot !!!

Snigdha

Greg Knaddison SSC Eights! Points: 859 More actions · Answer 5

Snigdha,

If everything that you have said is true then it seems you have a confusing table structure.

First post:

"The field TestRequestNo is a PK in TestRequest table and FK in Project table."

Second post:

"So the ProjectId, PK of Project table is a FK in TestRequest."

So you have one to many relationships going in both directions between the tables. This is probably not the correct structure of your data.

Either the structure is different from the way you stated it, or your structure is wrong.

Regards,

Greg