put a condition in join query

demoninside9

hi all below is my Store Proc

SELECT vb.Sno,
vb.Vehicle_Number,
vb.Vehicle_Booking_Date,
vb.Time_From,
vb.Time_To,
vb.Vehicle_Used_By,
vb.Cost_Code,
vb.Budget_Line,
lb.Vehicle_Number,
lb.Vehicle_Booking_Date,
lb.Time_From,
lb.Time_To,
lb.KM_Start,
lb.KM_End,
lb.Vehicle_Used_By,
lb.Cost_Code,
lb.Budget_Line
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno
where vb.Vehicle_Number = @Vehicle_Number
AND DATEPART(MONTH,vb.Vehicle_Booking_Date) = MONTH(getdate())

it is working fine. But here I need to put a condition, it should give priority to mtblLog_Book means if mtblLog_Book having data then it should show from mtblLog_Book table otherwise it should shows data from mtblVehicle_Booking for a particular Sno.

Actually firstly on the behalf of mtblVehicle_Booking I am binding a Gridview after updating the data it should show the data from mtblLog_Book.

So let me know please how to put a condition in my sp.

Thanks

Jorgen Andersson

Use a Case When or IsNull condition like this:

SELECT isnull(lb.somefield,vb.anotherfield) as columnname
FROM mtblVehicle_Booking vb
LEFT JOIN mtblLog_Book lb
ON vb.Sno = lb.Sno

If lb.somefield is null it will be replaced by vb.anotherfield

"The ones who care enough to do it right care too much to compromise." Matthew Faithfull

demoninside9

your query may be right, let me complete my sentence. according to you query it will show the not null columns, Yes I need that, but suppose I update vehicle_no in LogBook, now both Vehcile_Booking table and LogBook tabel both have Different Vehicle_no, but after updation I just want to show it from LogBook table instead of Vehicle_Booking table.

Mycroft Holmes

Did you actually try what Jorgen wrote, the isnull test does exactly what you are asking for:

select ISNULL(LogBook, Vehcile_Booking) from ..

. Will test logbook and return a non null result, if it is null then it will return vehicle_booking.

Never underestimate the power of human stupidity RAH

demoninside9

Mycroft Holmes wrote: if it is null then it will return vehicle_booking and if it is not null then, it will show value from LogBook?

Mycroft Holmes

No, if LogBook is not null then the not null value will be returned from logbook

Never underestimate the power of human stupidity RAH

demoninside9

SELECT vb.Sno,

        ISNULL(lb.Vehicle\_Number,vb.Vehicle\_Number) as  Vehicle\_Number,
         ISNULL(lb.Vehicle\_Booking\_Date,vb.Vehicle\_Booking\_Date) as  Vehicle\_Booking\_Date,
         ISNULL(lb.Time\_From,vb.Time\_From) as vb.Time\_To,
          ISNULL(lb.Time\_To,vb.Time\_To) as vb.Time\_To,
           ISNULL(lb.Vehicle\_Used\_By,vb.Vehicle\_Used\_By) as Vehicle\_Used\_By,
           ISNULL(lb.Cost\_Code,vb.Cost\_Code) as Cost\_Code,
            ISNULL(lb.Budget\_Line,vb.Budget\_Line) as Budget\_Line,	
     
       lb.KM\_Start,
       lb.KM\_End 
     
    
FROM   mtblVehicle\_Booking vb
       LEFT JOIN mtblLog\_Book lb
         ON vb.Sno = lb.Sno
            where vb.Vehicle\_Number = @Vehicle\_Number
            AND DATEPART(MONTH,vb.Vehicle\_Booking\_Date)  = MONTH(getdate())

showing error Incorrect syntax near '.'.