Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar questions other users answered. For Example : Let's say I want to see how many people in my Answer table answered age as 30 like Alex. So how can I join these two tables based on User.USERID="1" ? My expected result should be 1 since There is only one user "Tom" who is 30 years old and is "M" Male.

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),  
  QID INT,
  USERID INT);




INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

This is how I did it and it doesn't work :(

SELECT *
FROM Answer
JOIN Question
ON Answer.QID IN (SELECT Answer.QID FROM Answer WHERE Answer.USERID=3) 
   AND Answer.ATxt IN (SELECT Answer.ATxt FROM Answer WHERE Answer.USERID=3)
share|improve this question
add comment

1 Answer

When you join to tables together you need to think of it comparing each row from the first table to each row of the second table and then returning the ones that match based on the join condition. The join condition is specified in the ON clause.

In almost all cases you want to reference both tables in the ON clause. Let's look at your example in SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),  
  QID INT,
  USERID INT);




INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

The ON condition in your case is Answer.QID = Question.QID:

Query 1:

SELECT *
  FROM Answer
  JOIN Question
    ON Answer.QID = Question.QID
 WHERE Answer.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

You can use aliases to shorten that query a little:

Query 2:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
 WHERE A.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

The same way you can now also join in the User table:

Query 3:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
  JOIN User AS U
    ON A.UserId = U.UID
 WHERE U.UserName = 'Tom'

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER | UID | USERNAME |
|-----|------|-----|--------|--------|--------|-----|----------|
|   3 |   30 |   1 |      3 |    Age |      1 |   3 |      Tom |
|   6 |    M |   2 |      3 | Gender |      2 |   3 |      Tom |

For more information on JOINs check out my JOIN series here: A Join a Day

It is written about Microsoft SQL Server, but most of it works in other database management system too.

share|improve this answer
    
Thank you so much for offering a solution. I was wondering why Alex didn't show up in the user name field ? His info is 30 and M as well. I appreciate it if you elaborate on that. –  AlexCon Nov 21 '13 at 15:38
    
Because query 3 in my example explicitly filters on WHERE U.UserName = 'Tom' –  Sebastian Meine Nov 21 '13 at 17:22
    
Is there a way to get all users who answered the same questions Alex answered and Their answer was the same as Alex ? –  AlexCon Nov 21 '13 at 17:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.