Reputation: 273
I have got a table names
in MySQL with following columns ID, type, row, value
The composite primary key is ID, type, row
The purpose of this table is to save all names and professions of a specified person in multiple rows - one data per row.
For example: Commonly in Spain people have two first names and two last names, like José Anastacio Rojas Laguna
.
In germany, there are many persons having one first name but two last names. And even persons with wide profession, like teaching on university and working as a doctor in a hospital at the same time. In this case, in germany people would have trailing Prof. Dr.
in their names. For example: Prof. Dr. José Anastacio Rojas Laguna
In this case, I would store all these information in the table like this:
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
is given for one single person. Every people in the table have one unique ID
and even one person is identified by his ID
. type
defines as it says the type of the name. 0
means profession, 1
means first name and 2
means last name. row
defines the position in the name. 1
means 1st first name, 2
means 2nd firstname, 3
means 3rd firstname, etc... The same for profession and last name.
Now I would like to find out, how i can SELECT the ID
of a specified person by just passing some of the names of that person? How can I determine the ID by only giving a few of the values, which occur all in (or have) the same ID?
Upvotes: 4
Views: 309
Reputation:
So here's something using GROUP_CONCAT. Tested with your sample data and works.
It groups together all of the person's titles into a single column, their given name into another single column, and all their family names into a third column. It wraps each of those columns with commas to ensure finding a particular name is accurate.
The snippet below will find anyone who:
All you have to do to find a different user is change the WHERE clause.
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
Before edit, this may have not worked as intended. The extra commas ensure the name searched for is not found as a substring
Upvotes: 0
Reputation: 205
This will return users that have the name José Laguna with the same ID:
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é'
Upvotes: 1
Reputation: 48207
I use José
You could use a variable @searchText
SELECT *
FROM YourTable
WHERE ID IN (SELECT DISTINCT ID
FROM YourTable
WHERE value = 'José')
Or maybe use an IN
if multiple parameters
WHERE value IN ('José', 'Laguna')
Upvotes: 0