I have 4 different tables that I want to join. The tables are structured with columns as follows:
TableA - aID | nameA | dID
TableB - bID | nameB | cID | aID
TableC - cID | nameC | date
TableD - dID | nameD
Starting with Table A, I understand how to JOIN tables a and c using b, since b has the Primary Keys for those tables. I want to be able to join table TableD on TableA as well. Below is my SQL statement that first joins tables A and B, then joins that to C:
SELECT TableA.*, TableB.*, TableC.* FROM (TableB INNER JOIN TableA
ON TableB.aID= TableA.aID)
INNER JOIN TableC ON(TableB.cID= Tablec.cID)
WHERE (DATE(TableC.date)=date(now()))
When I attempt to add another join, to include D, I get an error that 'TableD' is unknown:
SELECT TableA.*, TableB.*, TableC.*, TableD.* FROM (TableB INNER JOIN TableA
ON TableB.aID= TableA.aID)
INNER JOIN TableC ON(TableB.cID= Tablec.cID)
INNER JOIN TableA ta ON(ta.dID= TableD.dID)
WHERE (DATE(TableC.date)=date(now()))
Simple INNER JOIN VIEW code....
CREATE VIEW room_view
AS SELECT a.*,b.*
FROM j4_booking a INNER JOIN j4_scheduling b
on a.room_id = b.room_id;
SELECT
a.nameA, /* TableA.nameA */
d.nameD /* TableD.nameD */
FROM TableA a
INNER JOIN TableB b on b.aID = a.aID
INNER JOIN TableC c on c.cID = b.cID
INNER JOIN TableD d on d.dID = a.dID
WHERE DATE(c.`date`) = CURDATE()
You have not joined TableD, merely selected the TableD FIELD (dID
) from one of the tables.
Source: Stackoverflow.com