CODEWITHSUNDEEP
sqlpostgresql
THpubs
THpubs

Reputation: 8172

In a PostgreSQL query how to filter results from a field in a join

My table structure is as follows:

Restaurants

| id | name    |
|----|---------|
| 1  | The Hut |
| 2  | T Burger|

Dishes:

| id | name    |
|----|---------|
| 1  | Pizza   |
| 2  | Caramel |

Orders:

| id | locatio |
|----|---------|
| 1  | New York|
| 2  | London  |

_RestaurantDishes: In here, B represents restaurants and A represents dishes. A restaurant can have many dishes. A dish can only have one restaurant.

| id | A | B |
|----|---|---|
| 1  | 1 | 1 |
| 2  | 1 | 2 |

_DishOrders: In here, B represents orders and A represents dishes. A dish can have many orders. An order can have many dishes.

| id | A | B |
|----|---|---|
| 1  | 1 | 1 |
| 2  | 1 | 2 |

What I want to do is, get a list of dishes from a selected list of restaurants and sort them according to the orders count. I tried to do it like this:

SELECT count(dishOrder.id) as myCount, dish.id, name
FROM "default$default"."Dish" dish
left join "default$default"."_RestaurantDishes" dishRestaurant on dish.id = dishRestaurant."A"
left join "default$default"."_DishOrders" dishOrder on dish.id = dishorder."A"
where "dishRestaurant"."B" in ("1", "2")
group by dish.id order by mycount desc;

But it gives me the error ERROR: missing FROM-clause entry for table "dishRestaurant". I tried many other approaches but didn't work.

Upvotes: 1

Views: 274

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Don't escape identifiers if you can avoid it. The escaping fixes the casing -- and strange things happen.

I suspect that you want:

select count(*) as myCount, d.id, d.name
from "default$default"."Dish" d left join
     "default$default"."_RestaurantDishes" dr
     on dr."A" = d.id and
        dr."B" in (1, 2) left join
     "default$default"."_DishOrders" do
     on d.id = do."A"
group by d.id, d.name
order by mycount desc;

Notes:

  • I simplified the table aliases and removed the double quotes from the aliases defined in the query.
  • You should remove the double quotes from the column names -- if you can.
  • I added d.name back into the group by. This is not needed if d.id is unique.
  • I use count(*), so no columns are referenced.
  • I moved the filtering condition to the on clause. This keeps the left join intact.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you are getting error ERROR: missing FROM-clause entry for table "dishRestaurant" because you used double quote in number in ("1", "2") which turned into column name for using double quote. you have to change it like in (1,2)

SELECT count(dishOrder.id) as myCount, dish.id, name
FROM "default$default"."Dish" dish
left join "default$default"."_RestaurantDishes" dishRestaurant on dish.id = dishRestaurant."A"
left join "default$default"."_DishOrders" dishOrder on dish.id = dishorder."A"
where "dishRestaurant"."B" in (1, 2)
group by dish.id ,name
order by myCount desc;

you also used dish.id, name in selection but not used name in group by, so you have to use that in group by

Upvotes: 1

Related Questions