Query to fetch ordered records in a join-CodePudding

I am having some issues creating a query to fetch some data depending on the result of a inner join. For example. I have three tables.

Table A [id,name,...]
Table B [id, tableAId, slug ...]
Table C [id, tableBId, status ...]

The table B has the table A ids as a foreign key, and table C as the table B ids as foreign key.

So, what I need is a query to return every record from table A with a matching record in table B and C depending on provided status and slug. For example:

SELECT * FROM tableA
(INNER JOINS WITH TABLE B AND C)
WHERE tableB.slug = 'foo' AND tableC.status = 'approved'

Also, the check in table C must only be made for the last created record (createdAt column). I need to get the last record (by created date) that matches in the tableB and tableC. For example: If I have two records with slug foo and one with status approved and the last one with pending the query is supposed to do not return nothing. This is because even so a record with status approved exists, it is not the last one to be created

So. Lets say we have the following data:

Table A:

id - tableBId - createdAt

1 - 4 - 2010-10-10

Table B:

id - slug - createdAt

4 - foo - 2010-10-10

Table C:

id - tableBId - status - createdAt

1 - 4 - approved - 2010-10-10
1 - 4 - pending  - 2010-10-11

In this case if i pass the filter slug = foo and status = approved the query should't return nothing because the last record in the tableC associated to the the slug foo is pending and not approved.

Thank you in advance.

CodePudding user response：

Are you looking for something like this:

select a.*
from table_a a 
inner join table_b b on a.tableBId=b.id
inner join (
            select tableBId,status,createdAt 
            from  table_c  where (tableBId,createdAt) in (select tableBId,max(createdAt) 
                                                          from table_c  
                                                          group by tableBId )) as c
on b.id=c.tableBId
and slug='foo' and status ='approved';

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/159

If you remove the condition status ='approved' it will return results.

Try with your data and let me know.

CodePudding user response：

SELECT * FROM tableA
(INNER JOINS WITH TABLE B AND C)
WHERE tableB.slug = 'foo' AND tableC.status = 'approved'
ORDER BY C.created DESC