0

I have a small MySQL database of a few over 100 residents in the complex where I live. To simplify things, it contains unit numbers, surnames, first names, phone numbers and email addresses. The email addresses are irrelevant to the question, but they explain why people with the same surname in the same unit have different entries..

Here's some sample data:

UNIT,SURNAME,FIRST_NAME,PHONE
1,Evans,Bob,12345
2,Smith,Jane,23456
2,Smith,Jim,23456
3,Jones,Joe,45678
4,Jones,Jason,98765
5,Wong,Bill,95147
5,Lee,Jane,95147

To summarise the above data:

  • Bob Evans lives on his own,
  • Jane & Jim Smith live together in the same unit and have the same phone number (but different email addresses, hence the separate entries),
  • Joe Jones and Jason Jones, despite sharing the same surname, live in different units and (obviously) have different phone numbers, and
  • Bill Wong and Jane Lee live together and have the same phone number, but have different surnames.

I want to create a select query that will generate a phone list (sorted by surname) that intelligently recognises two people with the same surname that share the same phone number (the fact that they share the same unit number is pretty much assumed, but I think is irrelevant to the actual MySQL query) and combine them into a single row in the result, while leaving people with different surnames but the same phone number separated. In other words, the result of a select on the above data would look like this:

UNIT,SURNAME,FIRST_NAME,PHONE
1,Evans,Bob,12345
4,Jones,Jason,98765
3,Jones,Joe,45678
5,Lee,Jane,95147
2,Smith,Jane & Jim,23456
5,Wong,Bill,95147

Some research led me to the following query which almost does what I want:

SELECT unit, surname, phone,
group_concat(first_name order by first_name SEPARATOR ' & ') AS name
FROM owners
GROUP BY surname;

However, that combines Joe Jones and Jason Jones under Joe Jones' phone and unit numbers:

3,Jones,Joe & Jason,45678

I'd like to refine that query to keep Joe Jones and Jason Jones is their own separate entries with their own phone numbers. If it helps, there is a primary key "id" column with each row.

Any suggestions or pointers would be welcome. Thanks very much.

Craig

Community
  • 1
  • 1
CraigH
  • 165
  • 2
  • 12

2 Answers2

0

I would try to add the unit or the phone column to the group by clause:

Example:

group by surname, unit

group by surname, phone

You can also add both columns:

group by surname, unit, phone

akrys
  • 563
  • 1
  • 3
  • 9
  • Thanks akrys, that did exactly what I needed. I'll just get the columns in the right order in the script I use to generate the actual list. – CraigH Dec 05 '16 at 04:41
0

Well I would think a bit different, first you need to group it by the UNIT then append FIRST_NAME for the same UNIT (if there is anything to append), since one should GROUP BY the column that actually separate the rows not a column that might separate them.

SELECT 
owners.unit, 
owners.surname, 
owners.phone, 
/* IF there is a second first_name from the join */ 
IF(LT.first_name IS NOT NULL, CONCAT(owners.first_name, ' & ', LT.first_name), owners.first_name)
FROM owners
/* JOIN the table again, based on the params below */ 
LEFT JOIN owners LT ON(LT.unit=owners.unit AND LT.first_name != owners.first_name)
GROUP BY owners.UNIT;

A working SQLFiddle

Max Allan Niklasson
  • 502
  • 4
  • 15
  • Thanks very much Max. Unfortunately your SQL combined Bill Wong and Jane Lee, who I want to keep separate in the results because of their different surnames. And the result is sorted by unit number; grouping by surname fixes that, but introducing a "where" clause in your SQL causes a problem when I try to select only those owners who actually want to be on the phone list (a variable I didn't include in the original problem, so I understand it not being in your solution). – CraigH Dec 05 '16 at 04:33