I am working on a project using Symfony2.8 and MySQL. I have 4 tables in my database that are users, quotes, articles, articlesquotes.
user table
<html>
<body>
<table border="1">
<tr>
<th>user_id</th>
<th>name</th>
<th>age</th>
</tr>
<tr>
<td>7</td>
<td>Alex</td>
<td>20</td>
</tr>
<tr>
<td>8</td>
<td>John</td>
<td>30</td>
</tr>
</table>
</body>
</html>
quotes table
<html>
<body>
<table border="1">
<tr>
<th>quote_id</th>
<th>user_id</th>
<th>reference</th>
</tr>
<tr>
<td>61</td>
<td>7</td>
<td>AE20</td>
</tr>
<tr>
<td>62</td>
<td>7</td>
<td>AE21</td>
</tr>
<tr>
<td>63</td>
<td>7</td>
<td>AE22</td>
</tr>
<tr>
<td>64</td>
<td>8</td>
<td>AE29</td>
</tr>
</table>
</body>
</html>
articlesquote
<html>
<body>
<table border="1">
<tr>
<th>id</th>
<th>quote_id</th>
<th>article_id</th>
<th>qte</th>
</tr>
<tr>
<td>58</td>
<td>61</td>
<td>2</td>
<td>7</td>
</tr>
<tr>
<td>59</td>
<td>62</td>
<td>3</td>
<td>8</td>
</tr>
<tr>
<td>60</td>
<td>63</td>
<td>1</td>
<td>9</td>
</tr>
<tr>
<td>61</td>
<td>63</td>
<td>2</td>
<td>10</td>
</tr>
</table>
</body>
</html>
articles table
<html>
<body>
<table border="1">
<tr>
<th>article_id</th>
<th>name</th>
</tr>
<tr>
<td>1</td>
<td>article1</td>
</tr>
<tr>
<td>2</td>
<td>article2</td>
</tr>
<tr>
<td>3</td>
<td>article3</td>
</tr>
</table>
</body>
</html>
now the relation between these tables is
user<--One-To-Many-->quote<--one-To-Many-->ArticlesQuote<--Many-To-One-->Article
I would like to get articles that are in all quotes of user number 7 ordered by quote_id
I would like to get articles that are in quote number 63 of the user number 7
thank you for helping me.
try this:
Select a.* From articles a
Inner Join
articlesquote aq On a.article_id=aq.article_id
Inner Join
quotes q On q.quote_id=aq.quote_id
Inner Join
user u On u.user_id=q.user_id
Where
u.user_id=7 and q.quote_id=63