Paul Neale Paul Neale - 3 months ago 9
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.