This is my first SQL query.
I have four tables that I want to use.
Table 1: DAT_ACCESSORY has INSPN_ID and ACC_ID and looks like this ( Every car inspected gets an inspected ID. ACC_ID is accessory ID. Each of those numbers specifies the accessory found in the car). Ex: 53 is AM/FM radio
Table 2: DAT_INPN has INSPN_ID and many other variables and looks like this:
INSPN_ID DAM_Count etc....
Table 3: REF_ACCESSORY has ACC_ID and ACC_DESC:
78 CD player
Table 4: DAT_vehicle and has VEH_VIN ( which is the vehicle vin) and other information about the vin ( like car model, year, series)...
I want to have a table this:
VIN Accessory 1 Accessory 2 Accessory 3.... Accessory n DAM_COUNT
34324 Yes No No Yes 12
32342 No Yes No No 23
So, first I have all the accessory descriptions in table 3. I have a total of 392 accessories. I need to put those as a row. Then, for each vin I need to specify whether that accessory was present or not. This is where the yes and no come into play. The way to know which accessories are in a specific vin is from table 2 which ties the INSPN_ID ( the inspection id) and specifies all the accessories that are for the ID.