Searching a column value that has couple records with some different specified values

I have a database:

|id|surname|name
| 1|Smith |John
| 2|Smith |Mike
| 3|Smith |Bob
| 4|Knope |John
| 5|Knope |Mike
| 6|Knope |Dick
| 7|Pratt |John
| 8|Pratt |Jill
| 9|Pratt |James


and I want to find a family name that has in it John, Mike and Bob. I want it to return Smith. Or I want to search for a family that has in it John and Mike and I want it to return Smith and Knope. How can I do that?

the results that I want to get are above but here's the same with nicer form:
I'm looking for family that has Jon, Mike, Bob. I want to get:

|surname|
|Smith |


Then I want to look for John and Mike only and I want to get:

|surname|
|Smith |
|Knope |


Do you want both results?
– Thomas
Jun 29 at 9:18

Specify the expected result. And show us your current query attempt.
– jarlh
Jun 29 at 9:23

Is it SQL-Server? Then please tag it.
– Thomas
Jun 29 at 9:25

Hint: GROUP BY, HAVING, COUNT().
– jarlh
Jun 29 at 9:26

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ...
– a_horse_with_no_name
Jun 29 at 9:29

postgresql

oracle

sql-server

db2

4 Answers
4

Based on your explantions you can do something like this:

declare @table table (it int, surename varchar(50),name1 varchar(50))

insert into @table

values
(1,'Smith','John'),
(2,'Smith','Mike'),
(3,'Smith','Bob' ),
(4,'Knope','John'),
(5,'Knope','Mike'),
(6,'Knope','Dick'),
(7,'Pratt','John'),
(8,'Pratt','Jill'),
(9,'Pratt','James')

select * from @table
where name1 in ('john','mike','bob') and surename = 'smith'

union

select * from @table
where name1 in('john','mike') and surename in ('smith','knope')


Result

Your input has dynamically multiple values. Thus, I will assume you have put all of the names you want the family to include in a table #i with field "name". Then, you want all families for which no name specified in your input is missing.

select *
from (select distinct surename from yourtable)surnames
where not exists
(
select 1 from #i
where not exists
(
select 1
from yourtable t
where
t.surename=surnames.surename
and #i.name=t.name
)
)


This works in SQL Server - written before your question was tagged with PostgreSQL.

Set up the test data:

DECLARE @Names TABLE (ID INTEGER IDENTITY, Surname VARCHAR(50), Forenames VARCHAR(50));

INSERT
@Names (Surname, Forenames)
VALUES
('Smith', 'John'),
('Smith', 'Mike'),
('Smith', 'Bob' ),
('Knope', 'John'),
('Knope', 'Mike'),
('Knope', 'Dick'),
('Pratt', 'John'),
('Pratt', 'Jill'),
('Pratt', 'James');


Declare a table variable containing the forenames you'd like to match. This acts as a parameter, so you should edit the values we insert to test the results:

DECLARE @ForenamesToSearch TABLE (Forenames VARCHAR(50));

INSERT
@ForenamesToSearch
VALUES
('John')
, ('Mike')
, ('Bob');


Finally we use GROUP BY and HAVING COUNT to ensure the number of names matches exactly.

SELECT
Surname
FROM
(SELECT DISTINCT Forenames, Surname FROM @Names) Names
INNER JOIN @ForenamesToSearch Forenames ON Names.Forenames = Forenames.Forenames
GROUP BY
Surname
HAVING
COUNT(1) = (SELECT COUNT(1) FROM @ForenamesToSearch);


Probably not the best way to do this but you can try the following for Postgresql:

select *
from
(
select
concat(',' , string_agg(name1,',') , ',') as X,
surname
from
table_name as A
group BY
surname
) As B
Where B.X like '%,John,%' And B.X like '%,Mike,%' And B.X like '%,Bob,%';


SQLFIDDLE DEMO

The following is for SQL server:

select * from
(
select
', ' + STUFF((SELECT ', ' + name1 FROM table_name WHERE surname = A.surname FOR XML PATH('')),1,2,'') + ',' as X,
surname
from
table_name as A
group BY
surname
) as B
Where B.X like '%, John,%' And B.X like '%, Mike,%' And B.X like '%, Bob,%';


SQLFIDDLE DEMO

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.