locked
Three tables compare and fetching the Vendor ID RRS feed

  • Question

  • User-46526262 posted

    I am a beginner , i wrote one query for vendor ID . Actually It displays three type of output.  but i need a single output with compare of three tables.

    Pls kindly reply me


    IF OBJECT_ID('SP_DELETE_VENDORID','P')IS NOT NULL
        DROP PROCEDURE SP_DELETE_VENDORID;
    GO
        CREATE PROCEDURE SP_DELETE_VENDORID
        @enclaveOID BIGINT   
    AS    
            BEGIN
                DECLARE @pending_amt Numeric(9)
                DECLARE @Date DATETIME
                DECLARE @doj DATETIME
                SELECT @pending_amt = pending_amt FROM tblVendorPayment WHERE EnclaveOID=@enclaveoid AND Status='A'
                SELECT @Date= AssignedDate FROM tblAssignedVendor WHERE EnclaveOID=@enclaveoid  AND Status='A'
                SELECT @doj = doj FROM tblVendor WHERE EnclaveOID=@enclaveoid AND Status='A'
        IF(@pending_amt IS NULL)    
                SELECT v.OID,v.supplier_id from tblVendor V
                inner join tblVendorPayment vp on vp.VendorOID=vp.OID    
                WHERE v.EnclaveOID =@enclaveOID AND v.Status='A'    
            END        
        IF(@Date IS NOT NULL)
            BEGIN
                SELECT v.OID,v.supplier_id from tblVendor V
                inner join tblAssignedVendor av on av.VendorOID=av.OID    
                WHERE v.EnclaveOID =@enclaveOID AND v.Status='A'
            END
        IF (@doj IS NOT NULL)    
            BEGIN
                SELECT OID,supplier_id from tblVendor WHERE EnclaveOID=@enclaveOID AND Status='A' AND OID
                NOT IN(SELECT  VendorOID from tblAssignedVendor WHERE EnclaveOID=@enclaveOID AND Status='A'
                UNION ALL
                SELECT VendorOID FROM tblVendorPayment WHERE EnclaveOID =@enclaveOID AND Status='A')
            END    
    GO

    Tuesday, November 18, 2014 1:09 AM

All replies

  • User269602965 posted

    Would be useful to see the three source table layouts, and the desired output.

    Tuesday, November 18, 2014 4:06 PM