How to select the same value identifier in the same column?

Question

I have a table namesin MySQL with the following columnsID, type, row, value

Composite primary key ID, type, row

The purpose of this table is to save all the names and professions of the specified person in several lines - one information per line.

For example: Usually in Spain people have two first names and two last names, for example José Anastacio Rojas Laguna.

In Germany there are many people having one name, but two surnames. And even people with a wide profession, for example, teaching at the university and at the same time working as a doctor in a hospital. In this case, in Germany, people will have Prof. Dr.in their names. For example:Prof. Dr. José Anastacio Rojas Laguna

In this case, I would save all this information in a table as follows:

 ID | type | row | value
 1  | 0    | 1   | Prof.
 1  | 0    | 2   | Dr.
 1  | 1    | 1   | José
 1  | 1    | 2   | Anastacio
 1  | 2    | 1   | Rojas
 1  | 2    | 2   | Laguna

An ID . ID, ID. type , . 0 , 1 2 . row . 1 , 2 , 3 .. .

, ID , ? , , ( ) ?

Answer 1

, José Laguna :

select t1.id, t1.name, t2.name
from yourTable t1
join (select * from yourTable
where name = 'Laguna') t2
on t1.id = t2.id
where t1.name = 'José'

Answer 2

José @searchText

 SELECT *
 FROM YourTable
 WHERE ID IN (SELECT DISTINCT ID
              FROM YourTable
              WHERE value = 'José')

, , IN,

              WHERE value IN ('José', 'Laguna')

Answer 3

, - GROUP_CONCAT. .

, . , .

, :

• ""
• "Rojas"

, , , WHERE.

SELECT n.ID,n.type,n.row,n.value
FROM names n
INNER JOIN (
    SELECT ID
    FROM (
        SELECT ID
        ,CONCAT(',',GROUP_CONCAT((CASE WHEN type=0 THEN value ELSE NULL END) ORDER BY value ASC),',') AS titles
        ,CONCAT(',',GROUP_CONCAT((CASE WHEN type=1 THEN value ELSE NULL END) ORDER BY value ASC),',') AS givenNames
        ,CONCAT(',',GROUP_CONCAT((CASE WHEN type=2 THEN value ELSE NULL END) ORDER BY value ASC),',') AS familyNames
        FROM `names`
        GROUP BY ID
    ) grouped
    WHERE grouped.givenNames LIKE '%,Jose,%' AND grouped.familyNames LIKE '%,rojas,%'
) people ON n.ID = people.ID

This may not work properly before editing. Extra commas ensure that the search name is not found as a substring