Post reply

SQL SYNTAX PROBLEM

  • September 12, 2006 at 1:01 am

    #94544

    I got a master table called Item_Master_Table  with fields

    a) Item_SlNo b) Item_Name c) Item_Type

    and another table as Item_Allocation with fields as a) Emp_no b) Emp_Name c) Faulty_ItemSLNO d) Date_OfRep e) Replacement_ItemSLNO f) Date_OfAlloc. Now when Iam using syntax like

    select a.Emp_no,a.Emp_Name,a.Faulty_ItemSLNO, b.Item_Name as Faulty_Item, a.Date_OfRep, a.Replacement_ItemSLNO, b.Item_Name as Replacement_ItemName,a.Date_OfAlloc from Item_Allocation a,  Item_Master_Table  b where a.Faulty_ItemSLNO=b.Item_SlNo and a.Replacement_ItemSLNO =b.Item_SlNo order by a.Emp_no;

    it is returning Zero Records. I don't know where I made mistake and what is the correct syntax.

     

  • September 12, 2006 at 3:57 am

    #659823

    I ma assuming that the replacement item is not same as Faultly Item Number. So Try this.

    select a.Emp_no,a.Emp_Name,a.Faulty_ItemSLNO,

     b.Item_Name as Faulty_Item, a.Date_OfRep, a.Replacement_ItemSLNO,

     c.Item_Name as Replacement_ItemName,a.Date_OfAlloc

    from Item_Allocation a,  Item_Master_Table  b ,Item_Master_Table  c

    where a.Faulty_ItemSLNO=b.Item_SlNo

    and a.Replacement_ItemSLNO =c.Item_SlNo order by a.Emp_no;

    Thanks

    Sreejith

  • September 12, 2006 at 4:02 am

    #659825

    i'd advise changing to the following

    select a.Emp_no,a.Emp_Name,a.Faulty_ItemSLNO,

    b.Item_Name as Faulty_Item, a.Date_OfRep, a.Replacement_ItemSLNO,

    c.Item_Name as Replacement_ItemName,a.Date_OfAlloc

    from Item_Allocation a

    left join Item_Master_Table b on a.Faulty_ItemSLNO=b.Item_SlNo

    left join Item_Master_Table c on a.Replacement_ItemSLNO =c.Item_SlNo

    order by a.Emp_no;

    and see what data isn't being populated - that should tell you which table doesn't join correctly. - then adjust the data and rewrite as INNER JOIN

    MVDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply