Question 89719 in PostgreSQL Question posted in
The official documentation can be found here.

What (*) hides behind itself in this code – Postgresql

I don’t get why when I SELECT pizzeria_name and person_name in FROM nested query it doesn’t work but when I put (*) instead i see two columns displayed on the screen with names person_name and pizzeria_name. How can i substitute (*) to see the same result?

SELECT ( SELECT person.name FROM person WHERE person.id = pv.person_id ) AS person_name,
       ( SELECT pizzeria.name FROM pizzeria WHERE pizzeria.id = pv.pizzeria_id ) AS pizzeria_name 
FROM ( 
  SELECT *
  FROM person_visits
  WHERE visit_date BETWEEN '2022-01-07' AND '2022-01-09' 
) AS pv 
ORDER BY person_name ASC, pizzeria_name DESC;

I tried everything pizzeria.id, pv.pizzeria_id etc but it doesn’t work

Any types of Joins are prohibited

That’s how my table is looks like
[1]: https://i.stack.imgur.com/vaWVB.png

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT ( SELECT person.name FROM person WHERE person.id = pv.person_id ) AS person_name,
       ( SELECT pizzeria.name FROM pizzeria WHERE pizzeria.id = pv.pizzeria_id ) AS pizzeria_name 
FROM ( 
  SELECT person_id, pizzeria_id
  FROM person_visits
  WHERE visit_date BETWEEN '2022-01-07' AND '2022-01-09' 
) AS pv 
ORDER BY person_name ASC, pizzeria_name DESC;

  2. 0

    Delete the subqueries and use a plain query:

    select
  person.name as person_name,
  pizzeria.name as pizzeria_name
from person_visits
join person on person.id = person_visits.person_id
join pizzeria on pizzeria.id = person_visits.pizzeria_id
where visit_date between '2022-01-07' AND '2022-01-09' 
order by 1, 2 desc

    Standard naming convention of columns has been assumed.

    If you absolutely must use subqueries:

    select
  (
    select person.name
    from person
    where person.id = person_visits.person_id
  ) as person_name,
  (
    select pizzeria.name
    from pizzeria
    where pizzeria.id = person_visits.pizzeria_id
  ) as pizzeria_name
from person_visits
where visit_date between '2022-01-07' AND '2022-01-09' 
order by 1, 2 desc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search