CODEWITHSUNDEEP
mysql
Camille Greselle
Camille Greselle

Reputation: 41

MySQL inner join on same table

I have the following database schema: http://pastebin.com/tET8Wj17

I would like to make a query, getting all the message when my user_id is equal to the receiver or sender_id, but also takes and fetch id_parent message.

I thought about using INNER JOIN, but can't really get this to work with the same table.

Upvotes: 0

Views: 1880

Answers (2)

Kickstart
Kickstart

Reputation: 21533

A self join is simple enough using an alias

SELECT *
FROM table1 a
INNER JOIN table1 b
ON a.id = b.parent_id

Upvotes: 2

dethtron5000
dethtron5000

Reputation: 10841

You can INNER JOIN on the same table - you just need to give each table a distinct alias.

SELECT t1.*, t2.*
FROM table1 as t1
INNER JOIN table1 as t2 ON t1.someField = t2.someField

Reflexive joins like this can cause performance issues - be careful and make sure you are able to keep your application performant.

Upvotes: 2

Related Questions