CODEWITHSUNDEEP
sqlpostgresqlgroup-byleft-join
user1775888
user1775888

Reputation: 3313

left join and group by

I have table like below two, I want to list location row base on been used in task table, and dont duplicate location.id, also order by column in task table.

I'm not sure how to solve it use group by or other method?
(I also check distinct before, but it limit select specific column, seems not what I want..)
how to solve it ? 

location
id | status | ...
1  | ...
2  | ...

id SERIAL NOT NULL

task
id | location_id | create_date | start_date | ...
1  | 1           | ...
2  | 1           | ...
3  | 2           | ...

id SERIAL NOT NULL
location_id integer DEFAULT NULL
create_date timestamp without time zone NOT NULL  
start_date date NOT NULL

need output result 

location  order by task.create_date
id | ...
1  | ...
2  | ...

query

I add select t.location_id it works to count, but I still have problem in select rows

count works

SELECT count(l.*)
   AS total_row_count 
   FROM location l
         LEFT JOIN (
           SELECT t.location_id
           FROM task t
           GROUP BY t.location_id
           ) t ON t.location_id  = l.id
        WHERE l.status = ANY($1::int[])

select 

SELECT
  l.* 
  FROM location l 
        LEFT JOIN (
          SELECT t.location_id, t.create_date
          FROM task t
          GROUP BY t.location_id
          ) t ON t.location_id  = l.id
       WHERE l.status = ANY($1::int[]) 
       ORDER BY t.create_date desc NULLS LAST, 
       l.name asc NULLS LAST, l.id desc NULLS LAST OFFSET $2 LIMIT $3

error:

column "t.create_date" must appear in the GROUP BY clause or be used in an aggregate function SELECT t.create_date, t.location_id

Upvotes: 1

Views: 23345

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28236

If you want to list the records and columns of table location only then you can use exists, like shown below:

select * from location l where exists (select 1 from task where location_id=l.id)

You can find a demo here.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

You can simply do a left join to get the location_id and corresponding number of rows in task table like this:

select l.id, count(t.location_id) times
from location l
left join task t
on l.id = t.location_id
group by l.id
order by max(t.create_date) desc;

If location_id is unique (probably PK) in your location table and you want to select all or more columns from that table, you can just put them in both the select and group by clause of the query.

Upvotes: 1

Related Questions