One to many JOIN to select only the max value.

[Bacardi_Coder]

My query is 38 lines long so here is the simple version.

Note: frequency is a number input by user from 0-720, 0 is the feature off.

$result = mysql_query("SELECT * FROM main_table
LEFT OUTER JOIN secondary_table ON main_table.cid=secondary_table.cid
WHERE
((frequency = 0) OR (fc_timestamp IS NULL) OR ('$current_timestamp'-frequency*3600 > fc_timestamp))
");

The data looks like this: (main_table cid is a unique id and in secondary there is a auto increment id column)

main_table
---------------
| cid* | a | b |
| 1  |  0 | 0 |
| 2  |  1 | 0 |
| 3  |  1 | 1 |
| 4  |  0 | 0 |
---------------

secondary_table
---------------
| cid | user_id | fc_timestamp |
| 1  |    5     |  1420417976  |
| 1  |    7     |  1420417999 |
| 2  |    9     |  1420417977 |
| 2  |    9     |  1420418976 |
| 2  |    111   |  1420419976 |
| 2  |    134   |  1420427976 |
| 3  |    111   |  1420417986 |
| 4  |    1001  |  1420417876 |
-------------------------------

What is happening is the query is pulling each line from the secondary table so I get multiple cid's as the result. I only want the row in the joined secondary table that is the maximum value of fc_timestamp.

[kicken]

You have to do a sub query that gets the maximum for each ID, then join against to get the specific row you're interested in using the maximum value.

 

Your table don't really help with knowing what exactly you want, so this is just a guess.

SELECT fields 
FROM main_table
LEFT JOIN ( 
   SELECT
      cid
      , MAX(fc_timestamp) as max_fc_timestamp
   FROM secondary_table
   GROUP BY cid
) maxResult ON maxResult.cid = main_table.cid
LEFT JOIN secondary_table ON secondary_table.cid = main_table.cid AND secondary_table.fc_timestamp = maxResult.max_fc_timestamp

Note that you'll still get duplicates if there are multiple rows with identical maximum fc_timestamp values. A unique constraint could mitigate this if appropriate. You'll want to make sure that you have an index on the cid and fc_timestamp columns so that the sub query will complete quickly based on the index.

[Bacardi_Coder]

Thank you! This was what I needed.