July 17, 2007 at 5:19 am
Hello everyone, i m using SQL Server2000, i need help in my query:
select s.ShiftID,dbo.ShiftStatus(s.ShiftStatus) as ShiftStatus,st.ShiftTypeName,
s.ShiftOpenDate,s.ShiftCloseDate, su1.FullName as CloseBy
--,su.FullName as OpenBY
from ShiftsInformation s
left outer JOIN SystemUsers su1 on s.ShiftClosedBy = su1.SystemUserID
INNER JOIN ShiftTypes st on st.ShiftTypeCode = s.ShiftTypeCode
--INNER JOIN SystemUsers su on s.ShiftOpenedBy = su.SystemUserID
where s.ShiftID = 148
TABLES
1) ShiftsInformation
ShiftID
ShiftTypeCode
ShiftStatus
ShiftOpenDate
ShiftOpenedBy
ShiftCloseDate
ShiftClosedBy
2)ShiftTypes
ShiftTypeCode
ShiftTyapeName
3)SystemUsers
SystemUserID
SystemUserName
Login
Password
Data of main table (1)Shifts Information
ShiftID ShiftTypeCode ShiftStatus ShiftOpenDate ShiftOpenedBy ShiftCloseDate ShiftClosedBy
146 1 0 2007-07-17 10:00:30.200 2 2007-07-17 12:00:03.000 2
147 2 0 2007-07-17 10:00:59.390 2 2007-07-17 12:30:03.000 2
148 3 1 2007-07-17 10:01:11.013 1 NULL NULL
as u see in the main table, i have null values in the ShiftClosedBy column , but when i run the above follwing query (note: uncomment the commented lines)it returns nothing,
but when i delete one join from this table(see comment lines),it shows the required data,plz help me, how i right my query as we need 2 joins from
SystemUsers table, coz both userID has info abt ShiftClosed and ShiftOpnedBy (coz shift closed and opened by UserID)
reply me asap.Its urgent
Thanx in Advance
July 17, 2007 at 5:28 am
HI there,
What values do you have the SystemUsers table?
It seems that the inner join is filtering the results to nothing, which makes me thing the systemUsers table has no records in it or at least no records with ShiftOpenedBy = 1 or 2.
Hope this helps.
If not please supply the data for all the tables and I can have another look at this for you.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 17, 2007 at 5:29 am
May be following
select s.ShiftID,dbo.ShiftStatus(s.ShiftStatus) as ShiftStatus,st.ShiftTypeName,
s.ShiftOpenDate,s.ShiftCloseDate, su1.FullName as CloseBy,
(select FullName from SystemUsers where SystemUserID = s.ShiftOpenedBy) as OpenBY
--,su.FullName as OpenBY
from ShiftsInformation s
left outer JOIN SystemUsers su1 on s.ShiftClosedBy = su1.SystemUserID
INNER JOIN ShiftTypes st on st.ShiftTypeCode = s.ShiftTypeCode
--INNER JOIN SystemUsers su on s.ShiftOpenedBy = su.SystemUserID
where s.ShiftID = 148
July 17, 2007 at 5:35 am
Confused...
If you gonna do a nested select in the main select section of your query. You may find that a left join in the main query will give you better results.
Was I right in saying that your table didn't have the id's you were joining on?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 17, 2007 at 5:49 am
Sorry. The null have column ShiftClosedBy
select s.ShiftID,dbo.ShiftStatus(s.ShiftStatus) as ShiftStatus,st.ShiftTypeName,
s.ShiftOpenDate,s.ShiftCloseDate, su1.FullName as OpenBY,
(select FullName from SystemUsers where SystemUserID = s.ShiftClosedBy ) as CloseBy
--,su.FullName as OpenBY
from ShiftsInformation s
left outer JOIN SystemUsers su1 on s.ShiftOpenedBy= su1.SystemUserID
INNER JOIN ShiftTypes st on st.ShiftTypeCode = s.ShiftTypeCode
--INNER JOIN SystemUsers su on s.ShiftOpenedBy = su.SystemUserID
where s.ShiftID = 148
July 18, 2007 at 8:56 am
I ran your query (without the UDF, since you didn't provide any code for that), and it returned one row (which is probably the expected result) with all of your original code uncommented. Your query referenced SystemUsers.FullName, but that column was listed in your original post, so I added it to the CREATE TABLE statement.
What does the UDF dbo.ShiftStatus(s.ShiftStatus) return?
Here is the test code I used:
/*
DROP TABLE ShiftTypes
DROP TABLE SystemUsers
DROP TABLE ShiftsInformation
*/
GO
CREATE TABLE ShiftTypes
(
ShiftTypeCode int
, ShiftTypeName varchar(40)
)
INSERT ShiftTypes (ShiftTypeCode, ShiftTypeName)
SELECT 1, 'Shift Type 1'
UNION ALL
SELECT 2, 'Shift Type 2'
UNION ALL
SELECT 3, 'Shift Type 3'
CREATE TABLE SystemUsers
(
SystemUserID int
, SystemUserName varchar(50)
, fullname varchar(50) -- this was referenced in the query
--, Login
--, Password
)
INSERT SystemUsers (SystemUserID, SystemUserName, fullname)
SELECT 1, 'UserId01', 'User 1'
UNION ALL
SELECT 2, 'UserId02', 'User 2'
UNION ALL
SELECT 3, 'UserId03', 'User 3'
CREATE TABLE ShiftsInformation
(
ShiftID int PRIMARY KEY
, ShiftTypeCode int
, ShiftStatus int
, ShiftOpenDate datetime
, ShiftOpenedBy int
, ShiftCloseDate datetime
, ShiftClosedBy int
)
INSERT ShiftsInformation (ShiftID, ShiftTypeCode, ShiftStatus, ShiftOpenDate, ShiftOpenedBy, ShiftCloseDate, ShiftClosedBy)
SELECT 146, 1, 0, '2007-07-17 10:00:30.200', 2, '2007-07-17 12:00:03.000', 2
UNION ALL
SELECT 147, 2, 0, '2007-07-17 10:00:59.390', 2, '2007-07-17 12:30:03.000', 2
UNION ALL
SELECT 148, 3, 1, '2007-07-17 10:01:11.013', 1, NULL, NULL
GO
SELECT s.ShiftID
-- , dbo.ShiftStatus(s.ShiftStatus) as ShiftStatus
, st.ShiftTypeName
, s.ShiftOpenDate
, s.ShiftCloseDate
, su1.FullName as CloseBy
, su.FullName as OpenBY
FROM ShiftsInformation s
LEFT OUTER JOIN SystemUsers su1
ON s.ShiftClosedBy = su1.SystemUserID
INNER JOIN ShiftTypes st
ON st.ShiftTypeCode = s.ShiftTypeCode
INNER JOIN SystemUsers su
ON s.ShiftOpenedBy = su.SystemUserID
WHERE s.ShiftID = 148
/*
RESULTS:
ShiftID ShiftTypeName ShiftOpenDate ShiftCloseDate CloseBy OpenBY
------- ------------- ----------------------- --------------- --------- ------
148 Shift Type 3 2007-07-17 10:01:11.013 NULL NULL User 1
(1 row(s) affected)
*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply