Hi, I need some help with querying some data in my database, it is a sqlite3 database but anything in sql should be pretty similar. I am working on a project that stores data about pieces of equipment (table: Equipment); each piece of equipment may have multiple usermanuals linked to it which is stored in another table (table: Manual); each piece of equipment may be assigned to one or multiple locations.Below is an example of how I have my tables set up:Table: EquipmentEquipmentID EquipmentName1 Hammer2 Wrench3 Tape4 Screwdriver
Table: ManualManualID EquipmentID ManualFilename1 1 hammerRevision1.pdf2 2 wrenchmanual.pdf3 1 hammerRevision2.pdf4 3 tapemanual.pdf
Table: LocationLocationID EquipmentID LocationName1 1 Car2 1 Garage3 2 Attic
So for example, say I had two hammers and one was in my garage and the other was in my car, I would have a hammer entry in the equipment table and two entries in the location table for the car and garage. that type of hammer has a usermanual stored in the manual table as well.I can't figure out how to get a table that will list each item in the manuals table and its location (if it has one).What I would like to get as a result of the query would be something like this:ManualID EquipmentName LocationName ManualFile1 Hammer Car hammerRevision1.pdf1 Hammer Car hammerRevision2.pdf2 Wrench Attic wrenchmanual.pdf3 Tape tapemanual.pdf
Can someone point me in the right direction? Right now I have something similar to this, but this will only list the equipment with a location and will ignore those without a location (the tape).SELECT Manual.ManualID as ManualID, Equipment.EquipmentName as EquipmentName, Location.LocationName as LocationName, Manual.ManualFileName as ManualFileFrom Manual INNER JOIN Equipment ON (Manual.EquipmentID = Equipment.EquipmentID) INNER JOIN Location ON (Manual.EquipmentID = Location.EquipmentID)ORDER BY Manual.ManualID DESC
complex relational query
Posted - 2013-07-02 : 02:06:28
