CODEWITHSUNDEEP
sql
lipao255
lipao255

Reputation: 61

Cant merge two queries with different columns

I'm studying SQL and somehow I'm stuck with a question. I have 2 tables ('users' and 'follows').

Follows Table

user_id follows date
1 2 1993-09-01
2 1 1989-01-01
3 1 1993-07-01
2 3 1994-10-10
3 2 1995-03-01
4 2 1988-08-08
4 1 1988-08-08
1 4 1994-04-02
1 5 2000-01-01
5 1 2000-01-02
5 6 1986-01-10
7 1 1990-02-02
1 7 1996-10-01
1 8 1993-09-03
8 1 1995-09-01
8 9 1995-09-01
9 8 1996-01-10
7 8 1993-09-01
3 9 1996-05-30
4 9 1996-05-30

Users Table

user_id first_name last_name school
1 Harry Potter Gryffindor
2 Ron Wesley Gryffindor
3 Hermonie Granger Gryffindor
4 Ginny Weasley Gryffindor
5 Draco Malfoy Slytherin
6 Tom Riddle Slytherin
7 Luna Lovegood Ravenclaw
8 Cho Chang Ravenclaw
9 Cedric Diggory Hufflepuff

I need to list all rows from follows where someone from one house follows someone from a different house. I tried to make 2 queries, one to get all houses related to follows.user_id and another one with all houses related to follows.follows and "merge" then:

select a.nome_id, a.user_id_house, b.follows_id, b.follows_house 
from ( select follows.user_id as nome_id
              , users.house as user_id_house
       from   follows inner join users 
                 ON users.user_id = follows.user_id
     ) as a,
     ( select follows.follows as follows_id
             , users.house as follows_house
       from  follows inner join users 
                 ON follows.user_id = users.user_id
     ) as b
where a.user_id_house <> b.follows_house;

The problem is that the result is like 400 rows, its not right. I have no idea how I could solve this.

Upvotes: 1

Views: 71

Answers (1)

P. Kirill
P. Kirill

Reputation: 11

Try this

SELECT follows.user_id, users.school, followers.user_id, followers.school FROM follows 
JOIN users ON follows.user_id=users.user_id
JOIN users as followers ON follows.follows=followers.user_id 
WHERE users.school <> followers.school

Note: Pay attention to naming in my answer

Thanks for correcting to Thorsten Kettner

Upvotes: 1

Related Questions