Check Syntex - 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)

Check Syntex

Author

Topic

prompratan
Starting Member

30 Posts

Posted - 2005-03-09 : 02:55:24

I don't known. This statement is wrong???

select e.emp_id, e.emp_name,
l.l_id,l.l_datefrom,l.l_dateto,l.l_timesum,t.lt_name
from tbl_employee e, tbl_leave l, tbl_leavetype t
where e.emp_id *= l.emp_id
and l.lt_id = t.lt_id

Error Message :
Server: Msg 303, Level 16, State 1, Line 1
The table 'tbl_leave' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Kristen
Test

22859 Posts

Posted - 2005-03-09 : 03:34:05

You are trying to JOIN tbl_leavetype to tbl_leave, but tbl_leave is OUTER JOINed to tbl_employee

You could use an OUTER JOIN for tbl_leavetype, or (if it is required IF and ONLY IF rows are present in tbl_leave) you could use the JOIN syntax instead of the "*=" syntax and bracket the syntax


SELECT e.emp_id, e.emp_name,
       l.l_id,l.l_datefrom,l.l_dateto,l.l_timesum,t.lt_name
FROM  tbl_employee e
       LEFT OUTER JOIN tbl_leave l
           ON l.emp_id = e.emp_id
       LEFT OUTER JOIN tbl_leavetype t
           ON t.lt_id = l.lt_id

or

SELECT e.emp_id, e.emp_name,
       l.l_id,l.l_datefrom,l.l_dateto,l.l_timesum,t.lt_name
FROM  tbl_employee e
       LEFT OUTER JOIN (tbl_leave l
           ON l.emp_id = e.emp_id
           JOIN tbl_leavetype t
               ON t.lt_id = l.lt_id)

(heopfully I've got my brackets in the right place!)
Kristen

prompratan
Starting Member

30 Posts

Posted - 2005-03-09 : 03:59:45

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources