CODEWITHSUNDEEP
sql
SalmonMode
SalmonMode

Reputation: 269

Comparing 1 column of non-unique values to another column of non-unique values

Not sure how to ask it properly so I'll try to visualize it. I have 2 columns lets say one is names and the names repeat( A couple Johns a few Lukes etc). The other column is pretty much languages but only a few(French, spanish, english, etc). Basically, each person can have multiple languages.

Im trying to find out if given a name, could I determine which other people are associated with all of the languages the original person is. So example being Mark knows French and Spanish. Using that, I would try and figure out who else knows French and Spanish. So if Jeremy knows French, Spanish, and German, he would show up in the results.

Any ideas?

So far I have

accept nameIn prompt 'Search for:'
select name from sc16temp intersect (select lang from sc16temp where name='&nameIn');

Upvotes: 2

Views: 135

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Looks like you want relational division with a reminder.

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table YourTable
(
  Name varchar(10),
  Lang varchar(10),
  primary key (Name, Lang)
);

insert into YourTable values('John', 'English');
insert into YourTable values('John', 'French');
insert into YourTable values('John', 'Spanish');
insert into YourTable values('John', 'Swedish');

insert into YourTable values('Mark', 'English');
insert into YourTable values('Mark', 'French');
insert into YourTable values('Mark', 'Spanish');

insert into YourTable values('Peter', 'English');
insert into YourTable values('Peter', 'French');

Query 1:

select T1.Name
from YourTable T1
  inner join YourTable T2
    on T1.Lang = T2.Lang
where T2.Name = 'Mark'
group by T1.Name
having count(T1.Lang) = (select count(Lang) 
                         from YourTable 
                         where Name = 'Mark')

Results:

| NAME |
--------
| John |
| Mark |

Upvotes: 1

Related Questions