Joining 4 tables

Forum Moderators: open

Message Too Old, No Replies

Joining 4 tables

typomaniac

5:43 pm on Sep 27, 2022 (gmt 0)

Hi, I'm just hoping for input out of a curiosity question. I'm no pro at databases but usually manage to get the job done.
I recently needed to connect to 4 tables simultaneously and after all kinds of searching which lead to seeing all kinds of methods of joining using left join, right join, etc.
I finally came up with an answer to my problem which (to my surprise) worked. The wording in the statement is very basic and I'm wondering if in the future it might cause me a problem which I don't need.
Here it is:

$sth = $dbh->prepare("SELECT table_1.*, table_2.*, table_13.*,table_14.*
FROM table_1
JOIN table_2 ON table_1.id = table_2.id
JOIN table_3 ON table_2.id = table_3.id 
JOIN table_4 ON table_3.id where table_1.id = '$_[0]'");

I'm even thinking maybe when I complete my current project to see just how many tables I can get away with.

LifeinAsia

5:07 pm on Sep 29, 2022 (gmt 0)

Besides the (likely) typos for "table_13" and "table_14" I see several issues:
- Selecting * from every table gives you more data than you need (ex- the id field for each table will be returned).

- JOIN table_4 ON table_3.id where table_1.id = '$_[0]'

doesn't throw an error? I'm guessing another typo and you meant

ON table_3.id = table_4.id

- Assuming JOIN is short notation for INNER JOIN in the DB you're using, you won't return any data for an id if it's missing from any of the tables. Not sure if it's an issue with the data you're using.

typomaniac

5:56 am on Sep 30, 2022 (gmt 0)

The $_[0] was just a placeholder for the demo--no actual variables or tables were named. Each of the named tables contains the same id that relates to the same user and the reason for selecting * is because there are few columns per table but all are used for making the operation work as it should. The reason for the different tables is because each has different types of info.
That said, are you saying they should be inner joined?
As I mentioned though, it is working perfect as intended though I'm just learning this RDBMS thing and questioning something I scratched my head over.
Thanks much for the reply.

LifeinAsia

4:42 pm on Sep 30, 2022 (gmt 0)

The reason for the different tables is because each has different types of info.

Will each user always have info in each of the tables? If not, use a LEFT JOIN. An INNER JOIN will return no data for a user if there isn't data for it in one of the tables.

see just how many tables I can get away with.

Make sure you have an index on the id field for each table. Otherwise, your queries will get slower and slower as you add more tables/data.

typomaniac

8:22 pm on Sep 30, 2022 (gmt 0)

Every user will have info in every column of each table although limited amount of users and not that much info per user populating the columns. Index's to go in place. Thank you for the information. This project has become very educational.

Joining 4 tables

Joining 4 tables

typomaniac

LifeinAsia

typomaniac

LifeinAsia

typomaniac

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week