Vfrdtyky

In Sqlite I have the following tables:

CREATE TABLE "Person" (Id INTEGER PRIMARY KEY,

"Age" INTEGER, "Email" TEXT)



CREATE TABLE "_Person_Name" (Id INTEGER PRIMARY KEY,

Owner INTEGER NOT NULL, Name TEXT,

FOREIGN KEY(Owner) REFERENCES Person(Id))

where the second table represents a list of strings for the name of a Person.

I would like to query Person.Id by matching names such that several conditions may match for in the same row or in different rows of _Person_Name.Name. For example, suppose Person.Id 1 has 2 associated rows _Person_Name.Name "John" and "Smith", both with _Person_Name.Owner=1. Then I'd like to have a query that returns exactly this Person.Id 1 based on searching for "John" and for "Smith". "John Wilson" or "Jonas Smith" should not be returned, but "John Theodore Smith" should be returned.

I tried the following:

SELECT Person.Id FROM Person 

INNER JOIN _Person_Name ON Person.Id = _Person_Name.Owner 

WHERE (Name LIKE 'John') AND (Name LIKE 'Smith');

But this doesn't work. It finds the person with each of the conditions separately, but the conjunction of both seems to apply to the same row only, so nothing is returned.

How can I search for both conditions such that they must both apply to the same person Id, but may match in different rows of the list table?

Edit: Here is an example of the schema with data. It's just an example, this is for an automated tool that deals with arbitrary schemas and associated 'list' tables.

Table Person

Id    Age    Email

==================

1      30    john@test.com

2      28    lucie@gmail.com

3      47    bob@gmail.com



Table _Person_Names

Id     Name   Owner (Foreign Key references Person.Id)

1      John   1

2      C.     1

3      Smith  1

4      Lucie  2

5      Smith  2

6      Bob    3

7      Smith  3

The query should return only Id 1, because only Person.Id 1 has both "John" and "Smith" in the table _Person_Names.

The problem is finding if there are 2 rows inside _Person_Names containing the values John and Smith in the column Name and having the same value in column Owner.

This has nothing to do with the table Person.

If 2 such rows can be found, then the value in column Owner is the Id in the table Person. Right?

Check this code:

SELECT Owner FROM 

(SELECT pn.Owner AS Owner, pn.Name AS Name1, p.Name AS Name2 

FROM _Person_Names AS pn 

INNER JOIN _Person_Names AS p ON (pn.Owner = p.Owner) AND (Name1 <> Name2))

WHERE (Name1 = 'John') AND (Name2 = 'Smith')

Since the first, middle, and last names are all in the same column, you could also try modifying your WHERE clause to:

WHERE Name LIKE 'John % Smith'

Same result as forpas and Namandeep_Kaur methods just less bulky

Using an AND operator in WHERE clause is not retrieving the correct result. You should use an OR operator

Edited part starts here

select Person.ID, Dusra_table.name

    from person, dusra_table

where Person.ID = Dusra_Table.Owner

    and (Dusra_Table.Name = 'John'

            OR  Dusra_Table.Name = 'Smith'

      )

    and Person.ID = ANY(Select Dusra_Table.Owner from Dusra_table where Name = 'John');

Dusra_Table is _Person_Names table