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

1

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

answered May 9, 2017 at 17:13

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using sub query in select clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related