CODEWITHSUNDEEP
sqlpostgresqljoin
napkinsterror
napkinsterror

Reputation: 1965

SQL Querying Incomplete Result

I am having trouble understanding what is wrong with this query or how to properly join it together.

I know that I am missing results from other queries that show both sides of the connections separately using two aliases for the same table.

Pretty much a friendship has two "id"s (source and target) which both map to the same column in the users table.

I believe it is the OR statement that is causing the invalid joining, and if so how is the joining suppose to be done. If not what is the problem?

SELECT DISTINCT u.id, u.first_name, u.last_name
  FROM friendships AS f, users AS u
  WHERE f.mutual = 'true' -- both are friends
  AND (u.id = f.source_id OR u.id = f.target_id) -- this line?
  AND f.created_at BETWEEN '2016-01-20' AND '2016-01-27' -- time period they became friends
  GROUP BY u.id, u.first_name
  ORDER BY u.first_name;

Is it the use of the word "DISTINCT?"

I appreciate the help, I have tried INNER and LEFT JOINS, but my mind is just blanking on this and I can't figure out how to get it to work.

Here is the query that IS WORKING and shows me more users than the above query:

SELECT f.source_id, u1.first_name AS s_firstname, u1.last_name AS s_lastname, f.target_id, u2.first_name AS t_firstname, u2.last_name AS t_lastname
  FROM friendships AS f, users AS u1, users AS u2
  WHERE f.mutual = 'true' 
  AND u1.id = f.source_id AND u2.id = f.target_id
  AND f.created_at BETWEEN '2016-01-20' AND '2016-01-27';

Friendship Table Definition:

enter image description here

User Table Definition:

enter image description here

Upvotes: 0

Views: 603

Answers (2)

joop
joop

Reputation: 4503

This will avoid any "duplicates" between u1 * u2. Either one (or more) friendship records exist (in the given period) or they don't.

SELECT u1.first_name AS s_firstname, u1.last_name AS s_lastname
        , u2.first_name AS t_firstname, u2.last_name AS t_lastname
  FROM users AS u1
  JOIN users AS u2
        ON EXISTS (
        SELECT 13 FROM friendships  f
        WHERE u1.id = f.source_id AND u2.id = f.target_id
        AND f.mutual = 'true'
        AND f.created_at BETWEEN '2016-01-20' AND '2016-01-27';
        );

Upvotes: 0

philipxy
philipxy

Reputation: 15118

You must either
1. UNION (distinct) the projections of your second query on source and target or
2. select distinct users where they are a source or target

SELECT DISTINCT u.id, u.first_name, u.last_name
FROM friendships AS f, users AS u
WHERE f.mutual = 'true' -- both are friends
AND (u.id = f.source_id OR u.id = f.target_id)
AND f.created_at BETWEEN '2016-01-20' AND '2016-01-27'
ORDER BY u.first_name;

Upvotes: 1

Related Questions