CODEWITHSUNDEEP
mysql
ChiCgi
ChiCgi

Reputation: 755

MySQL Find Full Name Across Two Rows

I have a MySQL table where I'm trying to find a person by his full name. The problem is that first and last name are stored on two separate rows in the table as shown here:

+-----------------+-------------+-------------+--------------+
| submit_time     | form_name   | field_name  | field_value  |
+-----------------+-------------+-------------+--------------+
| 1323463601.0947 | Online Form | firstname   | Paulo        |
| 1323463601.0947 | Online Form | lastname    | Hill         |
+-----------------+-------------+-------------+--------------+

How can I construct a query that will get a single result (if possible) by searching for Paulo Hill?

Also, the submit_time column should have the same value for both rows--that will be the one column value that is unique in the table.

Upvotes: 1

Views: 203

Answers (4)

JK.
JK.

Reputation: 5136

One alternative is to use GROUP_CONCAT if you know the order:

SELECT GROUP_CONCAT(field_value SEPARATOR ' ') 
FROM tab
GROUP BY submit_time
HAVING GROUP_CONCAT(field_value ORDER BY field_id ASC) LIKE '%Paulo,Hill%'

Upvotes: 0

zerkms
zerkms

Reputation: 254926

Supposing 'paulo hill' is a single search query:

    SELECT t1.*,
           t2.field_value
      FROM tbl t1
INNER JOIN tbl t2 ON t1.submit_time = t2.submit_time
       AND t2.field_name = 'lastname'
     WHERE t1.field_name = 'firstname'
       AND t1.field_value || ' ' || t2.field_value = 'paulo hill'

Note: this solution is sensitive to amount of spaces between first name and last name in search query

Upvotes: 2

Nesim Razon
Nesim Razon

Reputation: 9794

try this:

SELECT * FROM (
  SELECT submit_time, group_concat(field_value SEPARATOR  ' ')  AS fullname
  FROM TABLE_NAME
  GROUP BY `submit_time`
) AS innertable
WHERE fullname='Paulo Hill'

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

You could use a self-join:

SELECT t1.submit_time, t1.field_value, t2.field_value
FROM your_table t1
INNER JOIN your_table t2 ON t2.submit_time = t1.submit_time
WHERE t1.field_name = 'firstname'
AND t1.field_value = 'Paulo'
AND t2.field_name = 'lastname'
AND t2.field_value = 'Hill'

Upvotes: 4

Related Questions