Can someone point me in the right direction?

Given tables:


| ID | URI | Address |
|    |     |         |


| ID | Given Name | Surname | Outward Postcode  |
|    |            |         |                   |

I am trying produce a table that contains actor URI, first names, surnames and addresses of actors living at the same addresses on the list (I do not need to supply the address at query time, rather the duplicate values in the address column) on phpMyAdmin.

Below is my query construct so far. Despite all efforts, I have only been able to get it to run and return the entire URIs, first names, surnames and addresses. I am totally stuck on how to have it narrow down to only the actors living at the same address.

SELECT   `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`
FROM     `actor_uri_address`
    LEFT JOIN `actor_full_name_opc` ON `actor_full_name_opc`.`ID` = `actor_uri_address`.`ID`
GROUP BY `actor_uri_address`.`URI`, `actor_full_name_opc`.`Given Name`,
         `actor_full_name_opc`.`Surname`, `actor_uri_address`.`Address`

1 Answer 1

SELECT  GROUP_CONCAT(CONCAT(Given_name, ' ', Surname)),
    FROM actor_uri_address
    JOIN actor_full_name_opc USING(ID)
    GROUP BY Address
    HAVING COUNT(*) > 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.