0 votes
1 view
in SQL by (22.4k points)

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

1 Answer

0 votes
by (40.3k points)

You can try using the below code:

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. You just need to perform another join just like you have done before.

Note: In the above code many of the parentheses are removed, as they are not necessary in most of the cases you had used, they only add confusion when trying to read the code. Proper nesting is mandatory to make your code readable and separated out.

Related questions

0 votes
1 answer
0 votes
1 answer
+3 votes
1 answer
+3 votes
1 answer
asked Jul 3 in SQL by Tech4ever (22.4k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...