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

Question

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');

its within Oracle so SQL Plus, but I would rather keep it as basic as possible so I would rather not any PL SQL — SalmonMode
– SalmonMode, Commented Nov 9, 2012 at 11:09
Do you have anything that uniquely identifies each user? This can be done but the results would be useless without it. — itsbruce
– itsbruce, Commented Nov 9, 2012 at 11:42
"anything that uniquely identifies each user" Their name. The name can be considered to be unique to one person but it appears multiple times, once for each language they know. "can you show us the schemas for the relevant tables?" i.imgur.com/Hchsb.png thats all there is to it. Thats just a quick example of names and languages — SalmonMode
– SalmonMode, Commented Nov 9, 2012 at 11:53

Mikael Eriksson · Accepted Answer · 2012-11-09 12:40:03Z

1

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 |

answered Nov 9, 2012 at 12:40

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SalmonMode Over a year ago

yup that did it. Thanks! now to just ponder it and figure out how

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related