Problem Solved
I figured it out. I was right. It seems that I can't do a subquery with multiple fields. I had to break that query apart and it seems to work now. Here is the solution:
select k.site_id, s.name, st.date_opened,
(select concat(c.name_first,' ', c.name_last) as User from...