CODEWITHSUNDEEP
phpmysqlsql
user994461
user994461

Reputation: 530

Joining three tables in one query using MySQL

I have tables named

**Subscription Table**
-------------------------------
id    actors_id      user_id
-------------------------------
1       5               1
2       7               2
3       12              96
4       18              54

**Actors Content**
-------------------------------
id    contend_id       actors_id
-------------------------------
1      25               5
2      65               18
3      120              18
4      98               12

**Content Table**
-------------------------------
r_id     title       content_post
-------------------------------
25     abvg         xxxxxxxxx
65     djki         fffffffff
98     sdrt         rrrrrrrrr
120    fgty         tttttttty

So i need first to get actors_id from Subscription Table because i know user_id value, after that need to get from Actors Content table content_id and at the end get r_id from Content Table and display on site.

I have try something but this is definitiely not good query

SELECT Content.*, (SELECT Actors.contend_id FROM Actors WHERE Actors.contend_id = Content.r_id) as views FROM Content,Actors WHERE Actors.actors_id IN (SELECT Subscription.actors_id FROM Subscription WHERE Subscription.user_id = 96)

Upvotes: 1

Views: 33

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

I guess equivalent join query will look something like

SELECT c.*, a.contend_id as views 
FROM Content c
JOIN Actors a ON a.contend_id = c.r_id
JOIN Subscription s ON a.actors_id  = s.actors_id
WHERE s.user_id = 96

Also don't use old syntax to join your tables use explicit syntax with join keyword

Upvotes: 1

Related Questions