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
INSPN_ID ACC_ID
701 53
701 69
702 71
702 74
703 83
703 84
703 87
Table 2: DAT_INPN has INSPN_ID and many other variables and looks like this:
INSPN_ID DAM_Count etc....
701 12
702 38
703 33
704 0
705 12
706 0
707 5
Table 3: REF_ACCESSORY has ACC_ID and ACC_DESC:
ACC_ID ACC_DESC
53 A/C
52 Airbag
60 Navigation
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.