CODEWITHSUNDEEP
mysqlgroup-by
lcdservices
lcdservices

Reputation: 863

determine row returned when grouping

This question has been asked and answered elsewhere, but I have yet to find an actual, reliable, working solution. I need to create a query that joins two tables, groups them, but controls which row is returned. Here is a simplified version of the data structure:

tbl_phone.contact_id is a foreign key to tbl_contact.id. primary is a boolean flag to indicate which of potentially multiple phone records is the contact's primary record.

The basic goal is to query all phone records but always return the primary record, unless a specific phone number is queried. Let's say a contact has two phone records, one with "1234" in the number and one with "5678" in the number, and let's say the latter is marked as primary. If the user searches phone = %1234% -- no problem, we return that row. But if we are searching the contact name with no phone filter, it should always return "5678" -- the primary record.

The closest I've come is with a subquery:

SELECT *
FROM (
  SELECT c.id contact_id, c.name, p.phone, p.primary
  FROM tbl_contact c
  LEFT JOIN tbl_phone p
    ON c.id = p.contact_id
  WHERE [possibly searching name or phone]
  ORDER BY p.primary DESC
)
GROUP BY contact_id

So basically, the inner subquery gets all contacts + phone sorted by primary DESC -- so those flagged with a 1 would be listed first. The outer query and group selects a single record per contact. Yes -- I know that GROUP BY breaks the default ONLY_FULL_GROUP_BY mode rule, but let's just go with it for the sake of discussion.

As noted elsewhere, for example: Which row's fields are returned when Grouping with MySQL? ...the MySQL documentation indicates GROUP BY will return rows randomly. BUT... most people find the above to work successfully, because normally the GROUP BY will return the first row of the inner subquery.

However, in my situation, it's not returning that row reliably. And for the life of me, I can't seem to come up with an alternative solution. A couple additional notes:

UPDATE: We are running MySQL 5.7

Upvotes: 0

Views: 48

Answers (1)

Booboo
Booboo

Reputation: 44013

If you are looking for a specific phone number then your SQL would just be:

SELECT c.id, p.contact_id, c.name, p.phone, p.`primary`
  FROM tbl_contact c join tbl_phone p on c.id = p.contact_id
  WHERE phone = '212-555-1112'

See DB Fiddle

The other case is where you want to retrieve the primary phone numbers for all or one specific name. Consider the following subquery:

SELECT id, contact_id, phone, `primary`
FROM tbl_phone
WHERE `primary`

This selects all the primary phone numbers from tbl_phone. Then our query becomes:

SELECT c.id, p.contact_id, c.name, p.phone, p.`primary`
FROM tbl_contact c
LEFT JOIN
(
    SELECT id, contact_id, phone, `primary`
    FROM tbl_phone
    WHERE `primary`
) p on c.id = p.contact_id
WHERE name = 'Jane Doe' /* remove this WHERE CLAUSE to get all orimary phone numbers */

Remove the WHERE clause in the above SQL to get the primary phone numbers for all contacts.

See DB Fiddle

But why not simply:

SELECT c.id, p.contact_id, c.name, p.phone, p.`primary`
FROM tbl_contact c left join tbl_phone p
ON c.id = p.contact_id
WHERE /* condition */

Examples of the WHERE clause:

WHERE `primary` /* get all primary phone numbers */
WHERE name = 'Jane Doe' /* get all phone numbers for Jane Doe */
WHERE phone = '212-555-1212' /* get specific phone number */
WHERE name = 'Jane Doe' AND `primary` /* get Jane Doe's primary phone number */

For WHERE clauses 1, 3, and 4 you might as well be doing an INNER JOIN (actually the is not point in doing an OUTER JOIN unless there will be a case of a contact without at least one primary phone number).

I think you have made this more complicated than it really is.

DB Fiddle

Upvotes: -1

Related Questions