How to compare rows from same table in sql

Question

My tables are:

frequents(bar,drinker);
likes(beer,drinker);
serves(bar,beer)

I want to "select pairs of drinkers who frequents exactly the same bars".I think I can write that query using only the frequents table (as it has both bar and drinker column) using self joints I tried to do but couldn't get it.I don't mind using other tables too to get the exact query.The query must select drinkers who goes to the same bars only.In other words they should have all the the bars in common.The query must be in generalized form it should not depend on data that's why I didn't put any data.

DRINKER |     BAR  
____________________    
John    |     Hyatt       
Smith   | Blue     
William | Hilton   
John    | Geoffreys 
Smith   | Hyatt     
Joe     | Blue      
Mike    | Hilton    
William | Dublin        
Jeff    | Hilton        
Jake    | Hilton

This is my frequents table I need to select only Joe and Smith and also Jake and Jeff because they visit exactly the same bars.

I know is not the way you want but if you knew there was only going to be say 5 bars then you could have 5 select statements saying select drinker from frequents where bar = "bar1" and repeat that 4 more times — Howli
– Howli, Commented Feb 2, 2014 at 2:14
is the information you show the exact structure of the tables? No id anywhere? — PatomaS
– PatomaS, Commented Feb 2, 2014 at 2:15
@PatomaS There is no id and no primary key I know its pointless but it makes the queries more difficult that's why — user1613360
– user1613360, Commented Feb 2, 2014 at 2:17
@Howlin I don't want to use the data in my queries it should be in a general form. — user1613360
– user1613360, Commented Feb 2, 2014 at 2:18
Post sample data, desired output and your unsuccessful attempts — peterm
– peterm, Commented Feb 2, 2014 at 2:32

Gordon Linoff · Accepted Answer · 2014-02-02 03:21:12Z

1

THe easiest way in MySQL is to use group_concat() to put the values together and compare:

select fd.bars, group_concat(fd.drinker) as drinkers
from (select f.drinker, group_concat(f.bar order by f.bar) as bars
      from frequents f
      group by f.drinker
     ) fd
group by bars
having count(*) > 1;

EDIT

You can also do this using joins, but to do it right, you need a full outer join -- which MySQL does not support.

Another way is to count the number of bars that each goes to, do an inner join, and be sure that the counts match as well as the bars:

select f1.drinker, f2.drinker
from frequents f1 join
     frequents f2
     on f1.bar = f2.bar join
     (select f.drinker, count(*) as numbars
      from frequents f
      group by f.drinker
     ) fd1
     on f1.drinker = fd1.numbars join
     (select f.drinker, count(*) as numbars
      from frequents f
      group by f.drinker
     ) fd2
     on f2.drinker = f2.drinker
group by f1.drinker, f2.drinker
having count(*) = max(f1.numbars) and count(*) = max(f2.numbars);

edited Feb 2, 2014 at 3:21

answered Feb 2, 2014 at 2:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

user1613360 Over a year ago

The first query returns a null set but I have drinkers who visits only the same bars.

Gordon Linoff Over a year ago

@user1613360 . . . Fixed. (it was missing the group by in the subquery.)

user1613360 Over a year ago

U should replace fd with f everything else is fine.Thanks a ton.

fixnode · Accepted Answer · 2014-02-02 02:25:39Z

0

This may help, however I'm using my knowledge from Microsoft SQL server. The syntax may differ from MYSQL. That being said you would need to JOIN the tables into themselves.

SELECT F.Bars, F.Drinkers, FB.Drinker 
FROM frequents F 
JOIN frequents FB ON (FB.Drinkers = F.Bars)
WHERE FB.Drinkers = F.Drinkers

basically I join the same table to itself saying the FB.Drinkers contains the bars that drinkers go to and F.Drinkers is the list of people who drink. I'm not 100% on the syntax but the idea is correct.

I haven't done SQL in 4 months. if you don't use it, you loose it :)

answered Feb 2, 2014 at 2:25

fixnode

1073 gold badges9 silver badges32 bronze badges

1 Comment

user1613360 Over a year ago

First the join statement fails because drinkers and bars will never be equal.

PatomaS · Accepted Answer · 2014-02-02 02:57:46Z

0

fixnode's answer is pretty good, although it lacks 2 little details that I think are important, select only non repeated results and sort them in a useful way.

SELECT DISTINCT tbl_1.drinker, tbl_1.bar
FROM `frequents` AS tbl_1
LEFT JOIN frequents AS tbl_2 ON tbl_1.bar = tbl_2.bar WHERE tbl_1.bar = tbl_2.bar
ORDER BY tbl_1.bar;

This is what I used for testing

CREATE TABLE IF NOT EXISTS `frequents` (
  `bar` varchar(100) NOT NULL,
  `drinker` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `frequents` (`bar`, `drinker`) VALUES
('bar_1', 'drinker_3'),
('bar_2', 'drinker_3'),
('bar_3', 'drinker_4'),
('bar_4', 'drinker_1'),
('bar_5', 'drinker_1'),
('bar_1', 'drinker_5'),
('bar_2', 'drinker_4'),
('bar_3', 'drinker_3'),
('bar_4', 'drinker_2'),
('bar_5', 'drinker_1');

answered Feb 2, 2014 at 2:57

PatomaS

1,60119 silver badges25 bronze badges

1 Comment

user1613360 Over a year ago

I need to select drinkers who visit exactly the same bars for example if drinker_1 goes to bar_1 and bar_3 and drinker_2 goes to bar_3 then the output should be an empty set because drinker_2 doesn't go to bar_3.

Lawrence Thurman · Accepted Answer · 2014-02-02 03:09:09Z

0

Well you are not showing anyone desired output but if you add a table for the bar, then this will work for you

select f.bar, f.drinker from frequents f
inner join bar b on b.bar = f.bar

answered Feb 2, 2014 at 3:09

Lawrence Thurman

6978 silver badges15 bronze badges

1 Comment

user1613360 Over a year ago

My last line in the post is the desired output.

Collectives™ on Stack Overflow

How to compare rows from same table in sql

4 Answers 4

3 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related