using sub query in select clause

Question

I have the following SQL query which I want to use the value of public.account.phone_number for the LIKE clause of the sub query.

Unfortunately something is not working right since the query is returning 0 for total_responses when I replace '%public.account.phone_number' which a phone number in the database I get the correct value.

SELECT public.email_account.email, public.account.password, public.ad.city, public.ad.state, public.ad.age, public.ad.hotel, public.ad.insert_time, public.ad.ad_url, public.ad.active, public.ad.on_page, public.ad.paid, public.account.phone_number, (SELECT COUNT(*) FROM public.match where match_id LIKE '%public.account.phone_number') AS total_responses FROM public.account INNER JOIN public.ad ON public.account.id = public.ad.account_id INNER JOIN public.email_account ON public.account.email_account_id = public.email_account.id ORDER BY ad.active ASC, on_page DESC

Does it work if you add another wildcard - '%public.account.phone_number% ? — Alex
– Alex, Commented May 9, 2017 at 17:12

Gurwinder Singh · Accepted Answer · 2017-05-09 17:13:27Z

I guess you want to do concatenation with the outer field phone_number.

Try this:

SELECT public.email_account.email, public.account.password, public.ad.city, public.ad.state, public.ad.age, public.ad.hotel, public.ad.insert_time, public.ad.ad_url, public.ad.active, public.ad.on_page, public.ad.paid, public.account.phone_number, (SELECT COUNT(*) FROM public.match where match_id LIKE '%' || public.account.phone_number) AS total_responses FROM public.account INNER JOIN public.ad ON public.account.id = public.ad.account_id INNER JOIN public.email_account ON public.account.email_account_id = public.email_account.id ORDER BY ad.active ASC, on_page DESC

Collectives™ on Stack Overflow

using sub query in select clause

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related