mysql - How can I join multiple SQL tables using the IDs?


Translate

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())) 

All Answers
  • Translate

    You want something more like this:

    SELECT TableA.*, TableB.*, TableC.*, TableD.*
    FROM TableA
        JOIN TableB
            ON TableB.aID = TableA.aID
        JOIN TableC
            ON TableC.cID = TableB.cID
        JOIN TableD
            ON TableD.dID = TableA.dID
    WHERE DATE(TableC.date)=date(now()) 
    

    In your example, you are not actually including TableD. All you have to do is perform another join just like you have done before.

    A note: you will notice that I removed many of your parentheses, as they really are not necessary in most of the cases you had them, and only add confusion when trying to read the code. Proper nesting is the best way to make your code readable and separated out.


  • Translate
    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()
    

  • Translate

    You have not joined TableD, merely selected the TableD FIELD (dID) from one of the tables.


  • Translate

    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;