December 11, 2007 at 10:21 am
I had made this one stored procedure to return the results of a left join query and it returns the results of a inner join query.
Create Proc sp_employeeReviewed
@employeeID uniqueidentifier
as
select
Name,
Reviewed
from
tblemployee e
left join tblreviewed r
on e.employeeID = r.employeeID
where
e.employeeID = @employeeID
The tables:
create table tblemployee{
employeeID uniqueidentifier,
Name varchar(100)
}
Go
ALTER TABLE tblemployee
ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)
Go
create table tblreviewed{
employeeID uniqueidentifier,
reviewed datetime
}
Go
ALTER TABLE tblreviewed
ADD CONSTRAINT fk_tblreviewed_tblemployee_employeeID
FOREIGN KEY (employeeID)
REFERENCES tblemployee (employeeID) ON DELETE CASCADE
Go
Assumptions:
+ 1-1 relationship between the tblEmployee and tblReviewed tables.
+ SQL Server 2005
desired result:
Snow, John | Null
Smith, Jason | 12/07/2007
actual result:
Smith, Jason | 12/07/2007
The only other solution I could come up with is:
Create Proc sp_employeeReviewed
@employeeID uniqueidentifier
as
select
Name,
(
select Reviewed
from
tblReviewed r
where
e.employeeID = r.employeeID
) as Reviewed
from
tblemployee e
where
e.employeeID = @employeeID
Any explanation why the left join isn't returning the desired result?
December 11, 2007 at 10:42 am
It looks like it is working fine to me.
DECLARE @EmployeeId uniqueidentifier
SET @EmployeeId= NEWID()
INSERT INTO tblemployee VALUES ( @EmployeeId, 'Snow, John' )
EXEC sp_employeeReviewed @EmployeeId
SET @EmployeeId= NEWID()
INSERT INTO tblemployee VALUES ( @EmployeeId, 'Smith, Jason' )
INSERT INTO tblreviewed VALUES ( @EmployeeId, '12/07/2007' )
EXEC sp_employeeReviewed @EmployeeId
Might want to verify the WHERE in your stored procedure is as you have it posted above, and not like this...
[Code]
select Name, Reviewed
from tblemployee e
left join tblreviewed r on e.employeeID = r.employeeID
where r.employeeID = @employeeID
[/code]
If you have the left joined tables fields in an equality in the where clause it would in essence become an inner join.
December 11, 2007 at 11:20 am
Dominic,
Your code works for me as well. I second looking at what Joel has suggested with your WHERE clause. Also, can you show us your call to the stored procedure? It is impossible for your stored procedure as you have posted it to return to you your 'expected' results. The SP is designed to only return one row at a time and your expected results shows multiple rows.
December 11, 2007 at 11:27 am
Do both people share the same employeeID? If so, then something's wrong. If not, then the WHERE clause is filtering it out.
The left join returns something like this:
1 Snow, John | Null
2 Smith, Jason | 12/07/2007
But if you put 2 in the @employeeID, then it would filter out the first row and you'd only get the second.
What does this return?
select Name, Reviewed
from tblemployee e
left join tblreviewed r on e.employeeID = r.employeeID
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 11, 2007 at 11:38 am
Yeah, I had realized that the stored procedure would return one record only after posting it.
Here is how it's essentially set up in the actual situation I'm working on.
create table tblitems{
ID uniqueidentifier,
parent_ID uniqueidentifier,
item varchar(100)
}
Go
ALTER TABLE tblitems
ADD CONSTRAINT pk_item PRIMARY KEY (Id)
Go
create table tblanswers{
ID uniqueidentifier,
itemID uniqueidentifier,
answer varchar(50)
}
Go
ALTER TABLE tblanswers
ADD CONSTRAINT fk_tblanswers_tblitem_itemID
FOREIGN KEY (itemID)
REFERENCES tblitem (ID) ON DELETE CASCADE
Go
tblItem is a recursive table where the parent ID refers to the ID of the parent item in the table itself.
the stored procedure I have set up is:
Create Proc sp_itemsAnswered
@parent_itemID uniqueidentifier
as
select
item,
answer
from
tblitem iC
left join tblanswers a
on iC.ID = a.itemID
where
ic.parentID = @parent_itemID
itemA | null
itemB | null
itemC | Answered
tblItems: One parent item to many children items relationship
tblItems - tblAnswers: One child item to one answer relationship
December 11, 2007 at 1:34 pm
>>tblItem is a recursive table where the parent ID refers to the ID of the parent item in the table itself.
If something is recursive, you need to join it to itself to resolve the parent->child relationships.
I don't think the problem is the LEFT JOIN. I exepct the problem is you're not joining tblItem to itself.
You have supplied DDL for tables. And stored proc code. And desired results. But you also need to supply sample data rows for the items & answers table so we can understand what a "parent id" is supposed to yield.
December 11, 2007 at 2:52 pm
here's a sample of the data
tblItems
ID parentID item
------------------------------------ ------------------------------------ ---------
10636C40-FBA8-4546-BB77-202CB5591D3B 69BD5C9D-C178-49DC-A43B-DDB7B9C026ED itemA
5286F150-0238-409C-B345-91892F41DB3D 69BD5C9D-C178-49DC-A43B-DDB7B9C026ED itemC
33E6D1A0-410A-4E3F-9D6A-B44E962D1C67 69BD5C9D-C178-49DC-A43B-DDB7B9C026ED itemB
69BD5C9D-C178-49DC-A43B-DDB7B9C026ED NULL container
tblAnswers
ID ItemID Answer
------------------------------------ ------------------------------------ ---------
3EED9847-0A6E-4462-B2D0-2EE7BEF8DD22 5286F150-0238-409C-B345-91892F41DB3D Answered
I don't think I need to join the recursive table within itself if I'm simply querying for the children rather than not the parent. I'm using the parentID to query the children and then get the associated records of the children.
Edit: Corrected the sample data to correspond to the example provided above.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply