I have 3 tables and I want to know how can I do a triple join with this type of setup if at all?
First Table: LOCATIONS_TABLE
locationID
websiteID
locationCity
locationState
locationCountry
locationURL
Second Table: PREF_TABLE
pref_ID
Pref_LocationID
Pref_WebsiteID
Pref_SavedTitle
Third Table: WEBSITECATEGORY_TABLE
wbcatID
websiteID
WBLinkCategoryParentID
WBLinkTitle
WBLinkURL
the 2nd table is where the user has a store preference with a Pref_SavedTitle for their Locations they have saved for later reference.
The Current SQL Statement I have which only does a double inner join is...
SELECT
LOCATIONS_TABLE.LocationWebsiteID,
LOCATIONS_TABLE.locationCity,
LOCATIONS_TABLE.locationState,
LOCATIONS_TABLE.locationCountry,
LOCATIONS_TABLE.locationURL,
PREF_TABLE.Pref_SavedTitle
FROM PREF_TABLE INNER JOIN LOCATIONS_TABLE
ON PREF_TABLE.Pref_LocationID = LOCATIONS_TABLE.LocationID
WHERE PREF_TABLE.Pref_SavedTitle = 'AlabamaPREF'
This returns sample data of...
LocationWebsiteID = 2
locationCity = Mobile
locationState = Alabama
locationCountry = United States
locationURL = alabama.bmv.org
Pref_SavedTitle - AlabamaPREF
The 3rd table has the sample data of...
wbcatID = 1
websiteID = 2
WBLinkCategoryParentID = 0
WBLinkTitle = Alabama Resources
WBLinkURL = /alabama-resources
This does exactly what it needs to do which is returns me all of the LOCATION_TABLE items that equal the LocationID that was stored previously in the PREF_TABLE.
However I have a 3rd table that I need to add to this equation (Which is the Third Table) listed above. I need to be able to link the Third Table on the websiteID somehow so that it knows the WBLinkTitle and WBLinkURL are associated with that specific websiteID
On the final output... I need to be able to pull the results for the columns...
LOCATIONS_TABLE.websiteID,
LOCATIONS_TABLE.locationURL,
WEBSITECATEGORY_TABLE.WBLinkTitle,
WEBSITECATEGORY_TABLE.WBLinkURL
which based off my sample data would be...
websiteID = 2
locationURL = alabama.dmv.org
WBLinkTitle = Alabama Resources
WBLinkURL = /alabama-resources
SQLFIDDLE EXAMPLE