Select data from another table if field is present

[rayman0487]

First off, sorry for my title, I really have no idea how to title this question. It's getting late, so I'm just going to jump right in.

 

I have 2 tables -> customers and specification that contain the following:

 

+--------------+    +----------------+

| Customers |     | specification |

+--------------+    +----------------+

|   id             |      |  cust_id        |

|   name       |      |  field_id        |

|                   |      |  field_value  | 

+--------------+    +----------------+

 

specification

1 - 1

1 - 2 - awesome

2 - 1

2 - 3

3 - 1

3 - 2 - great

3 - 3

 

How can I Select CONCAT(customers.name,'|',customers.id) as customer_info and specification.field_value WHERE specification.field_id = 2   BUT if there is no field_id 2 then just not include that in my result?

 

What I'm after:

 

John|1, awesome

Paul|2

Ryan|3, great

 

I'm thinking this is simple, but my brain is not working well tonight.

 

Thank you so much for your help,

Ray

[kicken]

INNER JOIN with the specification table and then just include specification.field_id=2 in the WHERE clause. Rows that do not match that were clause will be dropped from the result set.

[Barand]

 

What I'm after:

 

John|1, awesome

Paul|2

Ryan|3, great

 

Those expected results would require a LEFT JOIN

SELECT CONCAT(c.name,'|',c.id) as customer_info, s.field_value 
FROM customers c
LEFT JOIN specification s
    ON c.id = s.cust_id
    AND s.field_id = 2