Can this be written without using a view and instead use a subquery or something

Question

CREATE VIEW myView AS  SELECT *
FROM `ce_userbadges`
WHERE ceub_userid =85;

SELECT *
FROM ce_badge
LEFT JOIN myView ON ceb_id = ceub_badgeid;

So it has to select all badges user 85 has and also the badges they dont have (hence the left join).

I tryed this but it does the userid filtering after the join

SELECT *  FROM ce_userbadges
right join ce_badge on ceub_badgeid = ceb_id
WHERE ceub_userid = 85

Answer 1

SELECT *  FROM ce_badge
LEFT JOIN ce_userbadges
ON ceub_badgeid = ceb_id AND ceub_userid = 85

Answer 2

You can simply put the code of the view where you use it:

SELECT *
FROM ce_badge
LEFT JOIN (
  SELECT *
  FROM `ce_userbadges`
  WHERE ceub_userid = 85
) x ON ceb_id = ceub_badgeid;