Left join on 1:M relation by maximum value in non joinable column

I am solving this kind of problem on PostgreSQL database using SQL.

I have 2 tables, for simplicity assume they have only these columns and have 1:M relation.

Table A:

Name	Type	Note
id	long	primary key
state	string	string holding enum value (for simplicity assume values X,Y,Z

Table B:

Name	Type	Note
id	long	primary key
a_id	long	foreign key to A table
max	long	column holding maximum of bid
user_id	long	user who bid this bid

What I am expecting from result of SQL:

Select all table A ids which are in state Z and user_id of table B record which is highest and user_id is equal to 1 (so if there are 3 records in table B with max column values (1,2,3), it select the row with max value 3)

Business request to clarify the question more:

Get me all A table row ids, that are in state Z and user with some variable ID (lets say 1 for simplicity) has the maximum value in all children in table B.

I´ve tried to make some LEFT JOIN ON a.id = b.a_id and I know there will be some AND and some inner select with MAX(). but I am not really sure how to perform select max() when I do not maxing the joining column.

Answer

SELECT * FROM
table_a a1 JOIN table_b b1 ON (a1.id=b1.a_id) WHERE (a_id,max) in
(SELECT a_id,max(max) FROM
table_a a JOIN table_b b ON (a.id=b.a_id) GROUP BY b.a_id) and b1.user_id=1;

Advertisement

Answer