Mysql Left Join With Limit 1 Not Returning Desired Result

mysql using limit in a left join not working properly

Try this:

Select 
    * 
from 
    patient 
left join 
    (SELECT
        id as pf_id, 
        MAX(date_created) as latest_followup_date, 
        Patient_id
     FROM 
        Patient_followup 
     GROUP BY 
        Patient_id) as pf
ON pf.Patient_id = patient.id

MySQL left join limit to one row

For the expected result mentioned in your edit I would change the left joins to inner joins and select only country name with a group by clause. Note the foreign key names in the on clauses, I think you have to clarify/correct your table structures:

SELECT 
    table1.country

FROM
   table1 JOIN table2 ON table1.id = table2.table1_id
   JOIN table3 ON table2.id = table3.table2_id
   JOIN table4 ON table3.id = table4.table3_id

GROUP BY
    table1.country

LEFT JOIN not returning nulls nor the expected amount of records in MySQL

My answer assumes that you only want to include those users' results, that actually took an exam (answered at least 1 question).

Since I do not know if you have any tables that shows who subscribed to what exam (you should if you do not), I have to create the list of users that took the exams by obtaining all distinct exam id - user id pairs in a subquery as a derived table.

Also, there is a disconnect between the exams and the questions table - probably there is a slots table that connects the two tables. Because of this disconnect, I'm forced to use the answers table in a derived table to join exams and questions. However, this means that questions asked at the different slots for the same exam cannot be distinguished from each other. Also, this means that if all users skipped the same question, then it will not show up in the results.

I inner join the question and the correct options from the distractors table on the derived tables and left join the users' answers on this. I will use parentheses in the joins to force MySQL to create the exams - users - questions - correct answers resultset first and then do the left join.

select t1.exam_id, t2.question_id, d.option, (d.option=a.answer) as correct, t1.user_id, a.answer
from
    (
     (select distinct exam_id, user_id from answers) t1
     inner join
     (select distinct exam_id, question_id from answers) t2 a on t1.exam_id=t2.exam_id
     inner join questions q on t2.question_id=q.id
     inner join distractors d on q.id=d.question_id and d.correct=1
    )
left join answers a on t1.exam_id=a.exam_id and t1.user_id=a.user_id and q.id=a.question_id

LEFT JOIN only first row

@Matt Dodges answer put me on the right track. Thanks again for all the answers, which helped a lot of guys in the mean time. Got it working like this:

SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
    SELECT artist_id
    FROM feeds_artists fa 
    WHERE fa.feed_id = f.id
    LIMIT 1
)
WHERE f.id = '13815'

Limit JOIN to 1 result (latest) MySQL

SELECT e.*, a.* 
FROM incedententry as e 
INNER JOIN incidenttypes 
  ON e.IncidentType = incidenttypes.IncidentTypeID 
LEFT JOIN (
  SELECT MAX(Timestamp) AS Timestamp, IncidentRef 
  FROM incidentaudit 
  GROUP BY IncidentRef -- you were missing this
) AS a 
  ON e.IncidentRef = a.IncidentRef;

How to use LEFT JOIN and limit result to one row based on a count

One way is using a CTE. Use ROW_NUMBER() OVER(...) to sort and rank results by post and largest number of votes. Then grab the largest one, i.e. where votes_rank = 1

SELECT users.*
     , fl.*
  FROM users
  LEFT
  JOIN ( SELECT f.user_id
              , f.ip_address 
           FROM failed_logins f
           JOIN banned b
             ON b.ip_address = f.ip_address
       ) fl
    ON fl.user_id = users.user_id
 ORDER BY users.user_id, fl.ip_address

 user_id      ip_address
 ----------   ------------
     111      127.0.0.1
     111      192.168.0.1
     222      127.0.0.1
     333      192.168.0.1
     444      4.4.4.4

 ip_address
 ----------
 127.0.0.1

user_id  user_id  ip_address
-------  -------  ----------
111      111      127.0.0.1
222      222      127.0.0.1
333      (null)   (null)
444      (null)   (null)
555      (null)   (null)