MySQL Question
MYSql Select user if other user found
I have a query like
SELECT
pf.id, pf.forwhom, pf.body,
u.firstname, u.lastname, u.email
FROM prayerfeed pf
JOIN users u ON pf.user_id=u.id
WHERE pf.id=100;
And this returns the feed item pf that I want as well as the user that posted that item.
What I also need is the users name that responded to that feed item and I know their id, lets call it 2.
This doesn't work but it might explain what I need.
SELECT
pf.id, pf.forwhom, pf.body,
u.firstname, u.lastname, u.email,
ru.firstname, ru.lastname
FROM prayerfeed pf
JOIN users u ON pf.user_id=u.id
SELECT FROM users ru WHERE id=2
WHERE pf.id=834;
How do I go about this?
Answer
You need an additional JOIN:
SELECT
pf.id, pf.forwhom, pf.body,
u.firstname, u.lastname, u.email,
u2.firstname, u2.lastname, u2.email
FROM prayerfeed pf
JOIN users u ON pf.user_id=u.id
JOIN users u2 ON u2.id = 2
WHERE pf.id=100;
You can simply use the ON clause to filter the user that responded to the feed item, using the already known id value.
Source (Stackoverflow)