Ok so after going over this multiple times I don't understand what I am missing
I have at the moment 3 tables:
Users Table:
ID | Username | Password
1 | Micky | 123
2 | Mouse | 145
Questions Table:
Question ID| Question_Title | Question | Rating | Category ID | User ID |
1 | Meaning of Life? | Same as Title | 100 | 2 | 1 |
2 | Foo is love? | Same as Above | 95 | 4 | 2 |
Answer Table:
Answer_ID| Answer | Answer_Rating | Answer_User_ID | Question_ID |
1 | YES | 1 | 1 | 1 |
And I have the following Query:
"SELECT u.username,
q.Question_Title,
q.Question,
q.Rating,
a.Answer,
a.Answer_Rating
FROM Questions q
join Users u on u.id = q.User_ID
join Answers a on a.Question_ID = 'q.Question ID'
WHERE q.Question_Title LIKE '%$Term%'";
This will get me the Username of the person who asked the question in addition to the Question Title, Question, Question Rating, Answer to the question and the Rating of the answer.
However what do i add to the query so that it will print out the username of the person who answered the question, via the foreign key Answer_User_ID?
No matter what I have tried, It will turn out empty, it will return the query in associative array and I am currently using print_r to display the array.
P.S: The table structure will grow and I would like to know if their is a consistent way of adding to the query to include fields and multiple foreign keys that will be added later.
You need another join to users:
SELECT u.username, q.Question_Title, q.Question, q.Rating,
a.Answer, a.Answer_Rating,
ua.username as AnswerUserName
FROM Questions q join
Users u
on u.id = q.User_ID join
Answers a
on a.Question_ID = q.`Question ID` join
Users ua
on ua.id = a.Answer_User_ID
WHERE q.Question_Title LIKE '%$Term%'";