CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8995

using sub query in select clause

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

Upvotes: 1

Views: 51

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

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

Upvotes: 1

Related Questions