error using subquery

Question

This query does not not work:

SELECT
  user.user_id, 
  user.lastname,
  (SELECT
    reg_fee 
   FROM event 
   where event_name = (SELECT event_joined from user)
FROM user WHERE user.registration_type=1 AND user.payment_status=1

What is the problem?

Answer 1

You're missing a closing parenthesis on the first inner SELECT, but I'm not certain this will get what you want anyway.

SELECT
  user.user_id, 
  user.lastname,
  (SELECT
    reg_fee 
   FROM event 
   where event_name = (SELECT event_joined from user)
  /* Close the parens and add an alias */
  ) AS reg_fee
FROM user 
WHERE user.registration_type=1 AND user.payment_status=1

This can be done a little more cleanly (and probably a lot faster) with a JOIN, assuming I gleaned the table structure correctly, and user.event_joined = event.event_name

SELECT
  user.user_id,
  user.lastname,
  SUM(reg_fee)
FROM user JOIN event ON event.event_name = user.event_joined
WHERE user.registration_type = 1 AND user.payment_status = 1
GROUP BY user.user_id, user.lastname

Answer 2

i suggest you are using this way which is more readable in my opinion:

select user.user_id, user.lastname,event.req_fee
join event on (event.event_name = user.event_joined)
from user
where user.registration_type=1 AND user.payment_status=1