confused about outer join [message #597035] |
Mon, 30 September 2013 06:30 |
rnemkul
Messages: 35 Registered: February 2006
|
Member |
|
|
I have following statement it create problem what is wrong on this statement any body knows solution
Rajendra
SELECT DISTINCT TBL_ITEM_ISSUE.ISSUE_MST_ID,TBL_ROOM_MST.ROOM_NO,
TBL_ROOM_MST.FLAT_NO, TBL_ROOM_MST.BLOCK_NO, TBL_ITEM_ISSUE.SPEC_ID SPEC_ID, TBL_ITEM_ISSUE.QUANTITY, TBL_ITEM_ISSUE.REMARKS, TBL_ITEM_ISSUE.ISSUED_TO,
TBL_ITEM_MST.ITEM_CD, TBL_ITEM_MST.DESC_NEP, TBL_ITEM_MST.ITEM_TYPE_ID,
TBL_ITEM_TYPE_MST.UPPERCODE, TBL_ITEM_DTL.ITEM_ID, TBL_MOBILIZATION_DTL.DEPARTMENT_ID, TBL_MOBILIZATION_DTL.PROJECT_ID,
TBL_MOBILIZATION_DTL.POSITION_ID, TBL_ITEM_ISSUE.ISSUED_DT_ENG, TBL_ITEM_ISSUE.REQ_ID, TBL_ITEM_ISSUE.ISSUE_ID
FROM vw_issue TBL_ITEM_ISSUE , TBL_ITEM_DTL, TBL_MOBILIZATION_DTL, TBL_ROOM_MST, TBL_ITEM_MST, TBL_ITEM_TYPE_MST --,TBL_REQUISITION_MST
outer join tbl_stock_adjustment
ON ((TBL_ITEM_ISSUE.issue_mst_id=tbl_stock_adjustment.rel_id) AND (tbl_item_issue.item_id=tbl_stock_adjustment=item_id))(
-- AND (TBL_ISSUE_MST.REQ_MST_ID = TBL_REQUISITION_MST.REQ_MST_ID)
-- AND (TBL_REQUISITION_MST.MOBILIZATIONID = TBL_MOBILIZATION_DTL.MOBILIZATION_ID)
(TBL_ITEM_ISSUE.ITEM_ID = TBL_ITEM_DTL.ITEM_ID)
AND (TBL_ITEM_issue.ISSUED_TO = TBL_MOBILIZATION_DTL.EMP_ID)
AND (TBL_MOBILIZATION_DTL.ROOM_ID = TBL_ROOM_MST.ROOM_ID)
AND (TBL_ITEM_DTL.ITEM_MST_ID = TBL_ITEM_MST.ITEM_MST_ID)
AND (TBL_ITEM_MST.ITEM_TYPE_ID = TBL_ITEM_TYPE_MST.ITEM_TYPE_ID))
AND (TBL_ITEM_MST.ITEM_GROUP IN ( 'M','N'))
|
|
|
|
Re: confused about outer join [message #597071 is a reply to message #597037] |
Mon, 30 September 2013 13:40 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
In addition to what cookie has said, the most obvious thing that would cause an error is the fact that you have not specified whether your outer join is left, right or full. And you have put all of your join predicates within a single ON clause. I would STRONGLY suggest that you go and actually read the documentation on joins.
[Updated on: Mon, 30 September 2013 13:42] Report message to a moderator
|
|
|
Re: confused about outer join [message #597167 is a reply to message #597071] |
Tue, 01 October 2013 07:52 |
|
ilikesql
Messages: 3 Registered: October 2013 Location: pune
|
Junior Member |
|
|
Syntax is incorrect for sure.
If you are going to use "JOIN" clause in statement you have to use it when you join master table with all child tables not just one of the child.
|
|
|