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.
Advertisement
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;