Select first record in a One-to-Many relation using left join

by

After playing around a bit, this turns out to be trickier than I’d expected! Assuming that table_b has some single column that is unique (say, a single-field primary key), it looks like you can do this:

SELECT table_a.code,
       table_a.emp_no,
       table_b.city,
       table_b.county
  FROM table_a
  LEFT
  JOIN table_b
    ON table_b.code = table_a.code
   AND table_b.field_that_is_unique =
        ( SELECT TOP 1
                 field_that_is_unique
            FROM table_b
           WHERE table_b.code = table_a.code
       )
;

More Related Contents:

  1. Select rows which are not present in other table
  2. Left Join With Where Clause
  3. Two SQL LEFT JOINS produce incorrect result
  4. SQL to LINQ with multiple join, count and left join
  5. dplyr left_join by less than, greater than condition
  6. Difference between left join and right join in SQL Server [duplicate]
  7. Query with LEFT JOIN not returning rows for count of 0
  8. Are “from Table1 left join Table2” and “from Table2 right join Table1” interchangeable?
  9. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  10. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  11. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  12. SQL SELECT from multiple tables
  13. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  14. LEFT JOIN query not returning all rows in first table
  15. Get Parent with multiple children in desc order
  16. Multiple INSERT statements vs. single INSERT with multiple VALUES
  17. Database Structure for Tree Data Structure [closed]
  18. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  19. sql ORDER BY multiple values in specific order?
  20. MySQL’s now() +1 day
  21. Oracle SQL – Identify sequential value ranges
  22. ColdFusion adding extra quotes when constructing database queries in strings
  23. How can I determine the status of a job?
  24. MySQL #1140 – Mixing of GROUP columns
  25. How can I select rows with most recent timestamp for each key value?
  26. postgresql – sql – count of `true` values
  27. PostgreSQL where all in array
  28. Converting Select results into Insert script – SQL Server
  29. Primary key/foreign Key naming convention [closed]
  30. sql pulling a row for next or previous row of a current row

Leave a Comment