You must Sign In to post a response.
  • Category: .NET

    How to inner join 2 tables and just simply use 3rd table without join?

    here is my procedure just look below,

    ALTER PROCEDURE [dbo].[Proc_insertdetails]
    @patient_id int ,
    @prod_name VARCHAR (50),
    @quantity float ,
    @batchno VARCHAR (30),
    @mrp float,
    @expiry_date datetime,
    @bill_rate float

    AS
    BEGIN

    INSERT into Patient_mstdtl (patient_id,prod_id, quantity, batchno, mrp,
    purchase_price, sale_price, sale_price2, bill_rate, Scan_code,
    global_trade_item_no, stocksum_id, expiry_date)

    SELECT @patient_id ,pm.prod_id, @quantity, @batchno, @mrp, sk.purchase_price, sk.sale_price,
    sk.sale_price2, @bill_rate, sk.scan_code,
    pm.global_trade_item_no, sk.stocksum_id, @expiry_date

    from Product_mst pm , stocksummary sk , Patient_mst pmt

    where pmt.patient_id = @patient_id and
    pm.prod_name = @prod_name and
    sk.batchno =@batchno and
    sk.mrp = @mrp and
    sk.expiry_date = @expiry_date
    END


    In the above procedure i want to inner join 2 tables "pm" and "sk" and just simple use 3rd table pmt for matching 1st statement of where condition . how can i do that Please give me a way.

    Thanks.
  • #761798
    You question is not very clear.

    But i think the query below should solve your problem

    Select <Your select list>
    From Product_mst pm
    Left Join stocksummary sk
    On ( pm.prod_name=sk.prod_name)
    Left Join Patient_mst pmt
    On ( pmt.patient_id = @patient_id)

  • #761801
    Hi Priyanka.

    SELECT column names
    FROM pm
    inner join sk
    on pm.id=sk.sid
    Join pmt
    on sk.id=pmt.pid
    order by pm.id

    Regards

    Sridhar Thota.
    If you learnt from defeat..
    You haven't really lost..

    Sridhar Thota.
    Editor: DNS Forum.

  • #761803
    I dont want to join pmt just want to join pm and sk

  • #761804
    It would be much better if you provide table structure of pm,sk, pmt (only relevent fields) and how their are related to each other.

    I dont want to join pmt just want to join pm and sk
    =>Could you tell me why you do not want to join pmt.

  • #761811
    Hi,

    As per your post you need to join pm and sk tables and pmt you don't want to include in join condition, then what's the use of using pmt table.?

    Is there any reason..?

    Whenever we use joins first off all we need to compare the matching fields in both the tables once matching columns done then filter it as you want. without doing this stuffs and all only for filtering you want to join tables that's not a good practice, if you want sample query instead of posting your procedure better to post your table structures and relation ship between the tables and with sample data, based on that we will give you some what better solution.

    hope you understood..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #761816
    1. You can join first two tables using inner join
    2. Then use the left outer join for third table.

    So you will get your required result.

    By Nathan
    Direction is important than speed

  • #761817
    Hi

    You don't want to join the table pmt?

    But the pmt table should be made use in that query? Is this your question?

    Regards

    Sridhar Thota.
    If you learnt from defeat..
    You haven't really lost..

    Sridhar Thota.
    Editor: DNS Forum.

  • #761822
    actually pmt used for input like last line

    Where pmt.patient_id =@patient_id.

    bt pmt doesnt have common column with sk and pm , Thats why, But forget it i changed earlier procedures for my database so now this working Without join.


    Thanks for replies.


This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.