CODEWITHSUNDEEP
mysqlsql
haiopaii
haiopaii

Reputation: 23

MySQL - subquery or joining multiple rows

I got a little struggle with my mysql query. Maybe someone can help me.

Tables:

contacts:

|id|phone|first_name|last_name|email|company|

custom_values:

|id|c_id|custom_field_id|value|

The table custom_values got different custom_field_ids

id 4 = mobile
id 5 = fax
id 20 = 2nd phonenumber

The current query gives me all information from my contacts table. 

concat(contacts.first_name, ' ', contacts.last_name, ' - ', contacts.company) as displayname,     contacts.phone, contacts.last_name, contacts.first_name, contacts.company, contacts.email
from contacts

Now I want to add the information from the custom_values table to the query above. Is it possible to add 3 rows, that adds me mobile, fax and the 2nd phonenomber to every contact?

I tried with the next query, but that doesn´t work.

SELECT 
    concat(contacts.first_name, ' ', contacts.last_name, ' - ', contacts.company) as displayname, contacts.phone, contacts.last_name, contacts.first_name, contacts.company, contacts.email,custom_values.value as mobile 
from custom_values 
join contacts on custom_values.customized_id = contacts.id 
where custom_values.custom_field_id=4

thanks guys.

Upvotes: 2

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

If you want additional rows, then your approach is fine. However, I think you want additional columns, not additional rows.

One approach is multiple LEFT JOIN:

select concat(c.first_name, ' ', c.last_name, ' - ', c.company) as displayname,
       c.phone, c.last_name, c.first_name, c.company, c.email, 
       cvm.value as mobile, 
       cvf.value as fax, 
       cvp2.value as second_phone 
from contacts c left join
     custom_values cvm
     on cvm.customized_id = c.id and
        cvm.custom_field_id = 4 left join
     custom_values cvf
     on cvf.customized_id = c.id and
        cvf.custom_field_id = 5 left join
     custom_values cvp2
     on cvp2.customized_id = c.id and
        cvp2.custom_field_id = 20;

Upvotes: 1

Related Questions