Sql querying Ids of a table inner joined with a 'list' table
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.
sql sqlite sqlite3
add a comment |
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.
sql sqlite sqlite3
add a comment |
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.
sql sqlite sqlite3
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.
sql sqlite sqlite3
sql sqlite sqlite3
edited Nov 15 '18 at 22:41
Eric '3ToedSloth'
asked Nov 15 '18 at 21:45
Eric '3ToedSloth'Eric '3ToedSloth'
1947
1947
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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')
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
|
show 1 more comment
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
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
add a comment |
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
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53328339%2fsql-querying-ids-of-a-table-inner-joined-with-a-list-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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')
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
|
show 1 more comment
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')
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
|
show 1 more comment
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')
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')
edited Nov 15 '18 at 23:20
answered Nov 15 '18 at 22:17
forpasforpas
16.4k3627
16.4k3627
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
|
show 1 more comment
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
I know, but that's not what I'm looking for at all. As I've stated, I do not want every name containing John OR Smith, I want to get only the Ids of persons whose name list contains both a "John" and a "Smith" row. Your query also returns "Lucie Smith", "John F Kennedy", etc. I'm only looking for "John C. Smith", "John Theodore Smith", "Bob John Smith Wilcrow", and so forth. (In case you wonder, this is for a tool that deals with arbitrary lists within arbitrary user-defined schemas in a non-Sql query language that is translated to the Sqlite backend.)
– Eric '3ToedSloth'
Nov 15 '18 at 22:29
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
This doesn't work. Each part of the name is in a Name column in a row of its own in table _Person_Name where the Owner field has the Person.Id of table Person as Owner.
– Eric '3ToedSloth'
Nov 15 '18 at 22:34
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
You mean that John and Smith are in different rows?
– forpas
Nov 15 '18 at 22:36
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
@Eric'3ToedSloth' see my edited answer
– forpas
Nov 15 '18 at 23:21
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
Thanks a lot for taking the time to answer. I didn't know that you can inner join the same table to itself, but with the renaming it makes sense. I will test your solution.
– Eric '3ToedSloth'
Nov 15 '18 at 23:54
|
show 1 more comment
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
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
add a comment |
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
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
add a comment |
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
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
answered Nov 15 '18 at 22:30
MapinTXMapinTX
1027
1027
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
add a comment |
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
No, they are not in the same column - they may be, but do not need to be. I'll try to update my question to make this more clear.
– Eric '3ToedSloth'
Nov 15 '18 at 22:35
add a comment |
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
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
add a comment |
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
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
add a comment |
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
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
edited Nov 16 '18 at 0:16
answered Nov 15 '18 at 21:57
Namandeep_KaurNamandeep_Kaur
12517
12517
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
add a comment |
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Not what I'm looking for, please see my other comment.
– Eric '3ToedSloth'
Nov 15 '18 at 22:31
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
Got it. Please check the edited answer. Thanks!
– Namandeep_Kaur
Nov 16 '18 at 0:17
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53328339%2fsql-querying-ids-of-a-table-inner-joined-with-a-list-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown