Mysql – SQL query based on foreign key relationship

MySQL

I have two tables, a Project and an Action:

Project has the following fields:

project_name

Action has the following fields:

action_name
project
status

There is a one-to-many relationship between Action and Project.

I need a query that returns all the Projects that ONLY have actions with statuses greater than 1 OR Projects with no actions at all.

I've never written a query that is dependent on a relationship. How might this be constructed?

Best Answer

Your solution seems correct. Here's another way that may be more efficient as it doesn't do a grouping. It can't show the MIN(status) though:

SELECT
    "id", "name" 
FROM 
    "testapp_project" AS p
WHERE
    NOT EXISTS
        ( SELECT *
          FROM "testapp_action" AS a
          WHERE p."id" = a."project_id"
            AND a."status" <= 1 
        ) ;

Related Solutions

Mysql – Getting distinct list of records based on the MAX of a column

^{This answer was originally posted by the OP as an edit to the question; it is now reposted as a Community Wiki answer.}

I was able to find an answer with some more searching in the archive.

https://stackoverflow.com/questions/1049702/create-a-sql-query-to-retrieve-most-recent-records

So the SQL I created from that post which works in my case is as follows...

SELECT tblTestActionStatus.ID_TestAction, StatusDateTime, ID_Status 
FROM tblTestActionStatus 
INNER JOIN
    ( 
        Select MAX(StatusDateTime) as LatestDate, ID_TestAction
        FROM tblTestActionStatus 
        Group By ID_TestAction
    ) SubMax 
on tblTestActionStatus.StatusDateTime = SubMax.LatestDate
and tblTestActionStatus.ID_TestAction = SubMax.ID_TestAction

MySQL – Select Rows Based on Latest Date with Multiple Joins

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle

Best Answer

Related Solutions

Mysql – Getting distinct list of records based on the MAX of a column

MySQL – Select Rows Based on Latest Date with Multiple Joins

Related Question