Home / Questions / 249330
Accepted
Juan Pablo B
Asked: 2020-03-28 10:10:19 +0800 CST 2020-03-28 10:10:19 +0800 CST

Duplicate records except for one column

  • 772

I have the following query

SELECT  users.name as Nombre, users.surname as Apellido, users.email, provinces.name as Provincia, contactInfos.contactType AS tipo, contactInfos.value as Numero
FROM clients 
INNER JOIN loans ON loans.client_id = clients.id
INNER JOIN loansTypes ON loansTypes.id = loans.loanType_id
INNER JOIN users ON users.id = clients.user_id
LEFT JOIN locations ON clients.location_id = locations.id
LEFT JOIN provinces ON provinces.id = locations.province_id
LEFT JOIN contactInfos ON contactInfos.id = clients.phone_id OR contactInfos.id = clients.cellPhone_id
WHERE loansTypes.display_name = "Sitio privado"

Where the table that gives me problems is:

LEFT JOIN contactInfos ON contactInfos.id = clients.phone_id OR contactInfos.id = clients.cellPhone_id

Why do I get the following

| nombre | apellido | email | provincia | tipoContacto | numero|
|--------|----------|-------|-----------|--------------|-------|
|  A     |    B     |[email protected]|    C      |   TELEFONO   |  345  |
|------------------------------------------------------|-------|
|  A     |    B     |[email protected]|    C      |   CELULAR   |   654  |
----------------------------------------------------------------

where type of contact is telephone or cell phone and I would like to have EVERYTHING in a single record

Name, surname, email, province, cell phone and telephone

mysql

1 Answers

  1. Best Answer

    Since you want to get the data in different columns, you can work around it by doing joins twice on the same table, once to get the phone and once to get the cell.

    Something like that:

    SELECT    users.name as Nombre
        , users.surname as Apellido
        , users.email, provinces.name as Provincia
        , contactCell.value as Celular
        , contactPhone.value as Telefono
   FROM clients 
        INNER JOIN loans ON loans.client_id = clients.id
        INNER JOIN loansTypes ON loansTypes.id = loans.loanType_id
        INNER JOIN users ON users.id = clients.user_id
        LEFT JOIN locations ON clients.location_id = locations.id
        LEFT JOIN provinces ON provinces.id = locations.province_id
        LEFT JOIN contactInfos AS contactPhone ON contactPhone.id = clients.phone_id 
        LEFT JOIN contactInfos as ContactCell ON ContactCell.id = clients.cellPhone_id
WHERE loansTypes.display_name = "Sitio privado"
    • 3