Build this query correctly or even searching for the correct answer

ArmArPa · 11 Feb 2016

I've been reading how to solve this issue, but since i lack the knowledge to either build this query correctly or even searching for the correct answer i'll have to ask here and hope you can help me out.

Assuming i have a table named VENDORS like this:

Insertar CODE, HTML o PHP:

ID|name   |phone   |phone_type
 1| Andy  |305332  |office   
 1| Andy  |999999  |fax    
 1| Andy  |555555  |cellphone       
 2| Jack  |222222  |office       
 2| Jack  |444444  |fax     
 3| Peter |234234  |office       
 3| Peter |434345  |cellphone

How do i build a query so i get this

Insertar CODE, HTML o PHP:

ID|name   |phone   |fax      |cellphone
 1| Andy  |305332  |999999   |555555
 2| Jack  |222222  |444444   |NULL       
 3| Peter |234234  |NULL     |434345

I was thinking something like this:

Insertar CODE, HTML o PHP:

SELECT 
    id,
    name, 
    CASE 
        WHEN phone_type = 'office' 
            THEN phone END AS phone
        WHEN phone_type = 'fax' 
            THEN phone END AS fax,
        WHEN phone_type = 'cellphone' 
            THEN phone END AS cellphone  
FROM `VENDORS`
GROUP BY id

But of course this is not working, not sure how to get what i want, it seems easy, but i'm totally lost.

lombervid · 11 Feb 2016

Y de cuando hablas solo Inglés? xD

Intenta algo como esto:

PHP:

SELECT v.name,
		v1.phone AS phone,
		v2.phone AS fax,
		v3.phone AS cellphone
	FROM VENDORS v
		LEFT JOIN VENDORS v1 ON v1.ID = v.ID AND v1.phone_type = 'office'
		LEFT JOIN VENDORS v2 ON v2.ID = v.ID AND v2.phone_type = 'fax'
		LEFT JOIN VENDORS v3 ON v3.ID = v.ID AND v3.phone_type = 'cellphone'
	GROUP BY v.ID;

ArmArPa · 11 Feb 2016

lombervid dijo:

Y de cuando hablas solo Inglés? xD

Intenta algo como esto:

PHP:

SELECT v.name,
        v1.phone AS phone,
        v2.phone AS fax,
        v3.phone AS cellphone
    FROM VENDORS v
        LEFT JOIN VENDORS v1 ON v1.ID = v.ID AND v1.phone_type = 'office'
        LEFT JOIN VENDORS v2 ON v2.ID = v.ID AND v2.phone_type = 'fax'
        LEFT JOIN VENDORS v3 ON v3.ID = v.ID AND v3.phone_type = 'cellphone'
    GROUP BY v.ID;

Gracias 🙂

rodrigomx · 11 Feb 2016

Lo mas recomendable en programacion es guardar todo en un arreglo y despues procesar la informacion, me parese que con esos Joins cargas mucho el servidor MySQL

Build this query correctly or even searching for the correct answer

ArmArPa

lombervid

ArmArPa

rodrigomx