| Author |
Topic |
|
zoom14151
Starting Member
34 Posts |
 Posted - 2009-04-18 : 15:57:43
|
| I have a table with vehicle requests and I need to run a store procedure to check if a vehicle is available for the selected date and vehicle type. I want to fill a dropdown list with the vehicles that have not been requested for the selected date and vehicle type selected. I do have a Veh_Info table with all the vehicle information with columns Veh_ID,Type,Veh_Year,Veh_Make,Veh_Model,Veh_Color The table that stores the vehicle requests has columns ID,Vehicle_No,Date_Requested,Vehicle_Type,Veh_Year,Veh_Make and so on |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-18 : 16:06:12
|
| If you want to list all vehicles that have not been requested, here is the query: (assuming that veh_id = vehicle_no)SELECT * FROM [veh_info] WHERE NOT EXISTS (SELECT vehicle_no FROM [veh_requests] WHERE vehicle_no = veh_id) |
 |
|
|
zoom14151
Starting Member
34 Posts |
Posted - 2009-04-19 : 11:57:40
|
| Veh_id is an autonumber but I can change the Veh_Request to store the ID number from Veh_Info Thanks |
|
|
|
zoom14151
Starting Member
34 Posts |
Posted - 2009-04-21 : 13:01:57
|
| Here's the stored procedure that I created but it's not working right. I have three vehicle types (Veh-a,Veh-b,Veh-c) and I want to select vehicles that are not in the database for @Date1 (4/10/2009) and @VehType (Veh-b) and not select Veh-a or Veh-c just veh-b.ALTER PROCEDURE dbo.CheckVehicles ( @Date1 datetime, @VehType NVarChar (20) )AS SELECT * FROM [vehicles_info] WHERE NOT EXISTS (SELECT veh_id,vehicle_no FROM [vehicle_request] WHERE Date1 = @Date1 and VehType = @VehType) RETURN |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-21 : 13:32:19
|
| SELECT * FROM [vehicles_info] WHERE Vehicle_No not in (SELECT distinct Vehicle_No FROM [vehicle_request] WHERE Date1 = @Date1) and VehType = @VehType==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-04-21 : 13:35:35
|
Put that join condition in there. I am not sure about your column names,, marking it with red:SELECT * FROM [vehicles_info] WHERE NOT EXISTS (SELECT veh_id,vehicle_no FROM [vehicle_request] WHERE Date1 = @Date1 and VehType = @VehType and [vehicle_request].VehId=[vehicles_info].VehId) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-21 : 13:39:16
|
| You need to include the VehType = @VehType outside the subquery - can leave it inside as well if you like.Think my query is simpler.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
zoom14151
Starting Member
34 Posts |
Posted - 2009-04-21 : 15:01:47
|
| Thank you very much that worked!!!! |
|
|
|
|
Stored Procedure Question