help with joins

  • 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

  • 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

  • i tried with full outer join but not getting the result

  • 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

  • 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

  • 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

  • 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