How to get multiple records against one record based on relation?

by

The original question was database specific, but perhaps this is a good place to include a more generic answer. It’s a common question. The concept that you are describing is often referred to as ‘Group Concatenation’. There’s no standard solution in SQL-92 or SQL-99. So you’ll need a vendor-specific solution.

  • MySQL – Use the built-in GROUP_CONCAT function. In your example you would want something like this:
select 
  o.ID, o.Address, o.OtherDetails,
  GROUP_CONCAT( concat(e.firstname, ' ', e.lastname) ) as Employees
from 
  employees e 
  inner join organization o on o.org_id=e.org_id
group by o.org_id
  • PostgreSQL – PostgreSQL 9.0 is equally simple now that string_agg(expression, delimiter) is built-in. Here it is with ‘comma-space’ between elements:
select 
  o.ID, o.Address, o.OtherDetails,
  STRING_AGG( (e.firstname || ' ' || e.lastname), ', ' ) as Employees
from 
  employees e 
  inner join organization o on o.org_id=e.org_id
group by o.org_id

PostgreSQL before 9.0 allows you to define your own aggregate functions with CREATE AGGREGATE. Slightly more work than MySQL, but much more flexible. See this other post for more details. (Of course PostgreSQL 9.0 and later have this option as well.)

  • Oracle – same idea using LISTAGG.

  • MS SQL Server – same idea using STRING_AGG

  • Fallback solution – in other database technologies or in very very old versions of the technologies listed above you don’t have these group concatenation functions. In that case create a stored procedure that takes the org_id as its input and outputs the concatenated employee names. Then use this stored procedure in your query. Some of the other responses here include some details about how to write stored procedures like these.

select 
  o.ID, o.Address, o.OtherDetails,
  MY_CUSTOM_GROUP_CONCAT_PROCEDURE( o.ID ) as Employees
from 
  organization o

More Related Contents:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. How to do a batch insert in MySQL
  3. Error Code: 2013. Lost connection to MySQL server during query
  4. MySQL Trigger after update only if row has changed
  5. difference between primary key and unique key
  6. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  7. MySQL Query to select data from last week?
  8. Using an Alias in SQL Calculations
  9. Can a table field contain a hyphen?
  10. Adding a leading zero to some values in column in MySQL
  11. How do I add a custom CHECK constraint on a MySQL table?
  12. View’s SELECT contains a subquery in the FROM clause
  13. Check for x consecutive days – given timestamps in database
  14. MySQL Insert Into from one Database in another
  15. How do I get the ID of multiple inserted rows in MySQL?
  16. How to insert data to MySQL with auto-incremented column(field)?
  17. Case statement in MySQL
  18. Replace Into Query Syntax
  19. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  20. Laravel join with 3 Tables
  21. Remove special characters from a database field
  22. Difference between two table structure
  23. MySQL query, MAX() + GROUP BY
  24. How to insert a file in MySQL database?
  25. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  26. Foreign key constraints: When to use ON UPDATE and ON DELETE
  27. How to use a user variables in MySQL LIKE clause?
  28. How to speed up sql queries ? Indexes?
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment