CODEWITHSUNDEEP
mysqlsql
salim_aliya
salim_aliya

Reputation: 273

How to SELECT same ID of values occuring in the same column?

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

Answers (3)

user5051310
user5051310

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:

  1. Has atleast one given name of "José" and
  2. Has atleast one family name of "Rojas"

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

BrettKB
BrettKB

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

Juan Carlos Oropeza
Juan Carlos Oropeza

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

Related Questions