I am working on a mysql query on a Drupal database that combines users and two different cck content types. I know people ask for help with group max requests all the time ... I've done my best, but I need help.

This is what I have so far:

# the artists
SELECT
    users.uid,
    users.name AS username,
    n1.title AS artist_name
FROM users
LEFT JOIN users_roles ur 
    ON users.uid=ur.uid
INNER JOIN role r 
    ON ur.rid=r.rid 
    AND r.name='artist'
LEFT JOIN node n1 
    ON n1.uid = users.uid 
    AND n1.type = 'submission'
WHERE users.status = 1
ORDER BY users.name;

      

        
        
        
      

    

This gives me data that looks like this:

uid username artist_name
1   foo      Joe the Plumber
2   bar      Jane Doe
3   baz      The Tooth Fairy

      

        
        
        
      

    

Also, I have this query:

# artwork
SELECT
    n.nid, 
    n.uid,
    a.field_order_value
FROM node n
LEFT JOIN content_type_artwork a 
    ON n.nid = a.nid
WHERE n.type = 'artwork' 
ORDER BY n.uid, a.field_order_value;

      

        
        
        
      

    

Which gives me data like this:

nid uid field_order_value
1   1   1
2   1   3
3   1   2
4   2   NULL
5   3   1
6   3   1

      

        
        
        
      

    

Additional Information:

• nid
  
  is the main key for the artwork
• each Artist has one or more works of art
• valid data for field_order_value
  
  - NULL, 1, 2, 3, or 4
• field_order_value
  
  not necessarily unique to every artist. An artist can have 4 pieces, all with field_order_value
  
  = 1.

What I want is the minimum string field_order_value

from my second query related to the artist information from the first query. In cases where field_order_value

it is not valuable information (either because the Artist used duplicate values ​​in his works, or left this field NULL), I need a string with the minimum nid

from the second request.

Decision

Using divide and conquer both strategy and mysql views as method and referring to this article on group max queries I solved my problem.

Create view

# artists and artworks all in one table
CREATE VIEW artists_artwork AS
SELECT
    users.uid,
    users.name AS artist,
    COALESCE(n1.title, 'Not Yet Entered') AS artist_name,
    n2.nid, 
    a.field_image_fid, 
    COALESCE(a.field_order_value, 1) AS field_order_value
FROM users
LEFT JOIN users_roles ur 
    ON users.uid=ur.uid
INNER JOIN role r 
    ON ur.rid=r.rid 
    AND r.name='artist'
LEFT JOIN node n1 
    ON n1.uid = users.uid 
    AND n1.type = 'submission'
LEFT JOIN node n2
    ON n2.uid = users.uid
    AND n2.type = 'artwork'
LEFT JOIN content_type_artwork a ON n2.nid = a.nid
WHERE users.status = 1;

      

        
        
        
      

    

Request a Submission

SELECT
    a2.uid, 
    a2.artist, 
    a2.artist_name, 
    a2.nid, 
    a2.field_image_fid, 
    a2.field_order_value
FROM (
    SELECT
        uid, 
        MIN(field_order_value) AS field_order_value
    FROM artists_artwork
    GROUP BY uid
    ) a1
JOIN artists_artwork a2
    ON a2.nid = (
        SELECT
            nid
        FROM artists_artwork a
        WHERE a.uid = a1.uid
            AND a.field_order_value = a1.field_order_value
        ORDER BY
            uid ASC, field_order_value ASC, nid ASC
        LIMIT 1
)
ORDER BY artist;