Joins - SQL Server Forums

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)

Joins

Author

Topic

Dbart
Starting Member

3 Posts

Posted - 2003-09-09 : 15:42:23

I am a novice at TSQL so forgive the beginner question.
Here is my stored procedure code. What I need is a left join on GRANTS.employeeid = Used.employee id and Grants.itemid = Usedid.grantid. I've got the concept but can't seem to find the syntax to get it in there.

CREATE PROCEDURE GetPTOComp
(
@EmployeeID int
)
AS

SELECT
GRANTS.ITEMID,
GRANTS.GRANTDATE,
GRANTS.HOURS,
USED.SUMOFHOURS,
GRANTS.HOURS - USED.SUMOFHOURS AS REMAINING,
D.NICK_NAME AS COWORKER

FROM

(SELECT ITEMID,EMPLOYEEID,GRANTDATE,GRANTTYPE,HOURS FROM PTOGRANTS WHERE GRANTTYPE = 'C'
GROUP BY ITEMID,EMPLOYEEID,GRANTDATE,GRANTTYPE,HOURS) GRANTS,

(SELECT EmployeeID, GrantID, Sum(Hours) AS SumOfHours
FROM PTOCompUsed
GROUP BY EmployeeID, GrantID) USED,

OPENQUERY(LAWSON,'SELECT EMPLOYEE,NICK_NAME,LAST_NAME,DATE_HIRED FROM EMPLOYEE') AS D

WHERE
D.EMPLOYEE = @EmployeeID
and d.employee = GRANTS.EmployeeID
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-09 : 15:45:58

left outer join Grants
on GRANTS.employeeid = Used.employee id
and Grants.itemid = Usedid.grantid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-09 : 15:48:44

Is this what you want:



SELECT 
	GRANTS.ITEMID, 
	GRANTS.GRANTDATE, 
	GRANTS.HOURS, 
	USED.SUMOFHOURS, 
	GRANTS.HOURS - USED.SUMOFHOURS AS REMAINING, 
	D.NICK_NAME AS COWORKER
FROM 
	(SELECT 
		ITEMID,
		EMPLOYEEID,
		GRANTDATE,
		GRANTTYPE,
		HOURS 
	 FROM 
		PTOGRANTS 
	 WHERE 
		GRANTTYPE = 'C' 
	 GROUP BY ITEMID,EMPLOYEEID,GRANTDATE,GRANTTYPE,HOURS
	 ) GRANTS
LEFT JOIN 
	(SELECT 
		EmployeeID, 
		GrantID, 
		Sum(Hours) AS SumOfHours 
	 FROM PTOCompUsed 
	 GROUP BY EmployeeID, GrantID
	 ) USED 
ON GRANTS.employeeid = Used.employeeid and Grants.itemid = Usedid.grantid
JOIN 
	OPENQUERY(LAWSON,'SELECT EMPLOYEE,NICK_NAME,LAST_NAME,DATE_HIRED FROM EMPLOYEE') AS D 
ON D.employee = GRANTS.EmployeeID
WHERE 
	D.EMPLOYEE = 1

I had to format your code so that I could see it better.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 15:50:51

What's the OLE DB datasource that you need to use OPENQUERY For?

is it a linked sql server?

Anyway...left joins for you would be something like

SELECT *
FROM (SELECT * FROM myTable WHERE some predicate) AS L
LEFT JOIN ( SELECT * FROM myTable2 WHERE some predicate) AS R
ON L.Key = R.Key

or something like that...gives you everything in L and all matching rows in R..

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-09 : 15:51:44

Damn, I'm just too slow...still in vacation mode I guess....

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources