August 1, 2013 at 2:37 am
i have 2 table with 1 to many relation
table1
hid ,refid cid
500 4 1200
501 4 1200
table2
id description refid cid type
500 de 0 1200 H
501 de 0 1200 H
502 desc1 4 1200 D
503 desc2 4 1200 D
504 desc5 4 1200 D
506 desc8 4 1200 D
1001 desc120 3 1200 D
1002 desc121 3 1200 D
and so on
i have a query with table2 joined with some other tables for which i get the output as
id description refid cid type
502 desc1 4 1200 D
503 desc2 4 1200 D
504 desc5 4 1200 D
506 desc8 4 1200 D
i need to join table1 such that
i get the output as
id description
500 de
501 de
502 desc1
503 desc2
504 desc5
506 desc8
i have the below
select ID, tenderdesc from table2 where cid=1200
and refid in(select refid from table1 where cid=1200)
or
id in (select hid from table1 where cid=1200)
when executed individually i get the result i need
but when joined with the existing query i dont get the needed output
August 1, 2013 at 3:03 am
You probably need a full outer join.
Also check the first link in my signature on how to ask questions on forums.
It will make life easier for people trying to help you.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2013 at 3:40 am
i tried with full outer join but not getting the result
August 1, 2013 at 3:41 am
Can you post the query you tried?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2013 at 3:57 am
SELECT
''As RefNo,
'' AS ERPItmDesc,
'' as TDesc,
'' as Item Description,
'' as Date,
0 as ID
FROM TBLDETAIL FM INNER JOIN TBLHDRDETAIL MD
ON MD.CID=FM.CID AND MD.ID=FM.MRPID LEFT OUTER JOIN TBLHDR MH
ON MD.BRNID=MH.BRNID AND MD.CID=MH.CID AND MD.HDRID=MH.ID AND MH.ITMID=FM.ITEMID
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
WHERE
FM.CID=1200 AND FM.YMONTH=201207 AND FM.MONTHSECTION=1
UNION
(SELECT
TENDERREFNO as RefNo,
I.ITNAME AS ERPItmDesc,
MH.TDESC as TDesc,
MH.Item Description,
FM.Date,
MH.ID
FROM TBLDETAIL FM INNER JOIN TBLHDRDETAIL MD
ON MD.CID=FM.CID AND MD.ID=FM.MRPID LEFT OUTER JOIN TBLHDR MH
ON MD.CID=MH.CID AND MD.HDRID=MH.ID AND MH.ITMID=FM.ITEMID
LEFT OUTER JOIN STITMMAS I
ON MH.ITMID=I.ITMID LEFT OUTER JOIN TBLMAS U
ON I.ITMUNITID=U.UNITID
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
WHERE FM.CID=1200 AND FM.YMONTH=201207 AND FM.MONTHSECTION=1
)
this is the query i am having currently
need to modify it to get the result
SELECT mh.ID, mh.tdesc
FROM TBLHDR mh
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
where mh.cid=1200
August 1, 2013 at 4:44 am
Regarding the desired output that you gave in your original question:
you can get that from the second table only, so why would you join at all?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 5:22 pm
Hi,
What are you trying to achieve?
Do you need only records from Table 2 those exist in Table 1?
Please clarify.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply