Select the foo in distinct bar from 2 tables

Question

Let's say I have two tables:

Table foo
===========
id | val
--------
01 | 'a'
02 | 'b'
03 | 'c'
04 | 'a'
05 | 'b'

Table bar
============
id | class
-------------
01 | 'classH'
02 | 'classI'
03 | 'classJ'
04 | 'classK'
05 | 'classI'

I want to return all the values of foo and bar for which foo exists in more than one distinct bar. So, in the example, we'd return:

val | class
-------------
'a' | 'classH'
'a' | 'classK'

because although 'b' exists multiple times as well, it has the same bar value.

I have the following query returning all foo for which there are multiple bar, even if the bar are the same:

select distinct foo.val, bar.class
from foo, bar
where foo.id = bar.id
and
(
    select count(*) from
    foo2, bar2
    where foo2.id = bar2.id
    and foo2.val = foo.val
) > 1
order by 
va.name;

score 2 · Accepted Answer · answered Oct 07 '14 at 18:26

2

select f.val, b.class
from foo f
join bar b on f.id = b.id 
where f.val in (
    select f.val
    from foo f
    join bar b on f.id = b.id
    group by f.val
    having count(distinct b.class) > 1
);

answered Oct 07 '14 at 18:26

FuzzyTree

32,014
3
54
85

Nice query +1 from me :) – John Ruddell Oct 07 '14 at 18:40
Simple and what I needed. As an aside, I'm taking a sql course, and our teacher can produce queries, but they're often 3 or 4 sub-queries deep and have a lot of excess...have you used any good materials for writing queries? – ironicaldiction Oct 07 '14 at 19:55
@ironicaldiction sorry, don't have any recommendations. I usually search the web (and often end up here) – FuzzyTree Oct 07 '14 at 22:14
@ironicaldiction to be honest it really just depends on what you are trying to do... I would recommend you try to understand those queries that your teacher does because sometimes they are necessary to be 3-4 levels deep... and if you have any issues with it always feel free to post a question and you can even tag one of us in it and we will help if possible :) – John Ruddell Oct 07 '14 at 23:44
Thanks Fuzzy and John! – ironicaldiction Oct 08 '14 at 16:13

score 1 · Answer 2 · edited May 23 '17 at 10:33

you can just use a subquery that has all duplicated to display each row using EXISTS like so.

SELECT f.val, b.class 
FROM foo f
JOIN bar b ON b.id = f.id
WHERE EXISTS
(   SELECT 1
    FROM foo 
    JOIN bar ON foo.id = bar.id
    WHERE foo.val = f.val
    GROUP BY foo.val
    HAVING COUNT(DISTINCT bar.class) > 1
);

Fiddle Demo

Generally exists executes faster than IN which is why I prefer it over IN... more details about IN VS EXISTS in MY POST HERE

score 1 · Answer 3 · answered Oct 07 '14 at 18:32

You can do a subquery to get the population from foo that meets the conditions (the names). Then join back to the two tables to get the information you want for output:

select f.val, b.class
from (select f.val
      from foo f join
           bar b
           on f.id = b.id
      group by f.val
      having count(distinct b.class) > 1
     ) bf join
     foo f
     on bf.val = f.val join
     bar b
     on f.id = b.id

score 1 · Answer 4 · answered Oct 07 '14 at 18:33

1

select f.val, b.class
  from foo f
  join bar b
    on f.id = b.id
  join (select f.val
          from foo f
          join bar b
            on f.id = b.id
         group by f.val
        having count(distinct b.class) > 1) v
    on b.val = v.val

answered Oct 07 '14 at 18:33

Brian DeMilia

13,103
1
23
33

Select the foo in distinct bar from 2 tables

4 Answers4