Combine multiple sql tables and multiple sql queries into one statement

Question

I have done research on using the join method, to join tables together, but I not only want to join tables, but also queries into one statement and I do not know how to join queries together into one.

I have three tables, one called friends, one called users and one called beers.

user table:

name | id

friends table:

userID | friendName

beer table:

userID | beer

I want to find all the friends for a given user, and query get a count of the unique beers each user and there friends have.

Previously I was using php to "build" sql statements to execute and this was making lots of sql calls in loops and was just messy. I know there has to be a more efficient way of doing this.

Do friends have their on id or just names associated with userid? — peterm
– peterm, Commented Mar 24, 2013 at 3:26
in the friends table just their names, I need their id from the user table to get their beer count from the beer table, as it only has their id's — Mike
– Mike, Commented Mar 24, 2013 at 3:30
Well then you need a way to associate friends to a user, because right now you don't have that in your schema — peterm
– peterm, Commented Mar 24, 2013 at 3:32

Adi Inbar · Accepted Answer · 2013-10-01 01:25:51Z

1

Working example: http://sqlfiddle.com/#!2/f1fdd/9

Build schema:

CREATE TABLE user(
  id int(11),
  name varchar(50)
);
CREATE TABLE userFriends(
  userID int(11),
  friendID int(11)
);
CREATE TABLE beer(
  beerID int(11),
  beerName varchar(50)
);
CREATE TABLE friendBeer(
  userID int(11),
  beerID int(11)
);
INSERT INTO user(id,name) VALUES(1,'friend 1');
INSERT INTO user(id,name) VALUES(2,'friend 2');
INSERT INTO user(id,name) VALUES(3,'friend 3');
INSERT INTO user(id,name) VALUES(4,'jeremy');

INSERT INTO userFriends(userID,friendID) VALUES(4,1);
INSERT INTO userFriends(userID,friendID) VALUES(4,2);
INSERT INTO userFriends(userID,friendID) VALUES(4,3);

INSERT INTO beer(beerID, beerName) VALUES(1, 'amstel light');
INSERT INTO beer(beerID, beerName) VALUES(2, 'bud light');
INSERT INTO beer(beerID, beerName) VALUES(3, 'miller');

INSERT INTO friendBeer(userID, beerID) VALUES(1, 1);
INSERT INTO friendBeer(userID, beerID) VALUES(2, 2);
INSERT INTO friendBeer(userID, beerID) VALUES(3, 3);
INSERT INTO friendBeer(userID, beerID) VALUES(4, 1);
INSERT INTO friendBeer(userID, beerID) VALUES(1, 3);

And the query:

SELECT U.id,
       U.name,
       (SELECT count(beerID)
        FROM friendBeer 
        WHERE userID = 4) AS "user beer count",
       U2.name AS 'Friends name',
       COUNT(FB2.beerID)
FROM user U
LEFT JOIN userFriends F
  ON U.id = F.userID
LEFT JOIN user U2
  ON F.friendID = U2.id
LEFT JOIN friendBeer FB2
  ON F.friendID = FB2.userID
LEFT JOIN friendBeer FB
  ON U.id = FB.userID
WHERE U.id = 4
GROUP BY F.friendID, U.id

I'm assuming you will change =1 to whatever the UserID is - but this shows you how the query works.

edited Oct 1, 2013 at 1:25

Adi Inbar

12.4k13 gold badges60 silver badges71 bronze badges

answered Mar 24, 2013 at 2:57

What have you tried

11.2k4 gold badges35 silver badges45 bronze badges

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

14 Comments

Mike Over a year ago

awesome that helps, but it doesn't look like it includes the user whos friends I am checking for.

peterm Over a year ago

@Evan Please learn how to format code in your posts

What have you tried Over a year ago

@peterm ... it was just a rushing mistake ... but thanks for the link

Mike Over a year ago

I am not getting the results I want. I want the total beers for each user. I am getting the same number for each user.

What have you tried Over a year ago

Define "user" then ... because we're only pulling results for one user (from the user table) in that query.

|

ntstha · Accepted Answer · 2013-03-24 03:49:26Z

create table user(user_id int(11),user_name varchar(100));
create table friend(user_id int(11),friend_to int(11));
create table beer(user_id int(11),beer_name varchar(100));

insert into user values(1,'Evan');
insert into user values(2,'Mike');
insert into user values(3,'Roxetta');
insert into user values(4,'Rajesh');

insert into friend values(1,2);
insert into friend values(1,3);

insert into beer values(1,'sam bud');
insert into beer values(2,'carlsberg');
insert into beer values(2,'sam bud');
insert into beer values(2,'sam bud');

and the sql query

SELECT user.user_name, COUNT(DISTINCT(beer.beer_name)) `count` FROM beer JOIN `user` ON user.user_id=beer.user_id WHERE user.user_id=1
UNION
SELECT user.user_name,COUNT(DISTINCT(beer.beer_name)) FROM beer JOIN `user` ON user.user_id=beer.user_id 
WHERE user.user_id IN(SELECT friend_to FROM friend WHERE user_id=1);

Is this what you looking for?

Collectives™ on Stack Overflow

Combine multiple sql tables and multiple sql queries into one statement

2 Answers 2

14 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related