CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
Blakedallen
Blakedallen

Reputation: 664

Postgres Join Query is SOMETIMES taking the cartesian product

I'm attempting to join multiple tables for one query and I am getting inconsistent results from the database, I believe my query is taking the cartesian product of all the users, when I only want users who are in the DirectConversation.

The Schema for reference:

This is the schema for the tables in question, is it possible this is an error with my schema?

The query is (where $id stands for the variable User.id):

SELECT c.*, count(dm.id), 
u1.first_name, u1.last_name, u1.company, u1.picture, 
u2.first_name, u2.last_name, u2.company, u2.picture
FROM "DirectConversation" as c, "DirectMessage" as dm, "Profile" as u1, "Profile" as u2
WHERE u1."id_User" = c."id_User1"
AND u2."id_User" = c."id_User2"
AND c.id = dm."id_DirectConversation" 
AND dm.viewed = 'f' AND dm.deleted = 'f'
AND c."id_User1" = $id OR c."id_User2" = $id
GROUP BY c.id, u1.id, u2.id;

The expected result (the result when the user id = 1 ):

id | id_User1 | id_User2 | count | first_name | last_name |      company       |                                   picture                                   | first_name | last_name |    company     |                                 picture                                  
----+----------+----------+-------+------------+-----------+--------------------+-----------------------------------------------------------------------------+------------+-----------+----------------+--------------------------------------------------------------------------
  1 |        1 |        2 |     3 | Albert     | Einstein  | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg | Nikola     | Tesla     | Teslas Widgets | http://upload.wikimedia.org/wikipedia/commons/7/79/Tesla_circa_1890.jpeg
(1 row)

(END)

The error result (the result when the user id= 2):

id | id_User1 | id_User2 | count | first_name | last_name |      company       |                                                    picture                                                     | first_name | last_name |      company       |                                                    picture                                                     
----+----------+----------+-------+------------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------+------------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------
  1 |        1 |        2 |     4 | Albert     | Einstein  | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg                                    | Albert     | Einstein  | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg
  1 |        1 |        2 |     4 | Albert     | Einstein  | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg                                    | Nikola     | Tesla     | Teslas Widgets     | http://upload.wikimedia.org/wikipedia/commons/7/79/Tesla_circa_1890.jpeg
  1 |        1 |        2 |     4 | Albert     | Einstein  | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg                                    | Rosalind   | Franklin  | DNA R US           | http://upload.wikimedia.org/wikipedia/en/9/97/Rosalind_Franklin.jpg
  1 |        1 |        2 |     4 | Albert     | Einstein  | alberts inventions | http://upload.wikimedia.org/wikipedia/commons/d/d3/Albert_Einstein_Head.jpg                                    | Charles    | Babbage   | Babbages Cabbages  | http://upload.wikimedia.org/wikipedia/commons/6/6b/Charles_Babbage_-_1860.jpg

... Note this was truncated for brevity. I believe this is taking the cartesian product of all the users, however I am unaware as to why

The version of postgres I'm using:

version

-----------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

Upvotes: 1

Views: 179

Answers (1)

Jeremy C.
Jeremy C.

Reputation: 2465

I just moved your where clauses for the joins to the ON statement and made proper joins, if this doesn't work I'll set up a sqlfiddle and see what the problem with this sql is

SELECT c.*, count(dm.id), 
    u1.first_name, u1.last_name, u1.company, u1.picture, 
    u2.first_name, u2.last_name, u2.company, u2.picture
    FROM "DirectConversation" c
    JOIN "DirectMessage" dm ON c.id = dm."id_DirectConversation" 
    JOIN "Profile" u1 ON u1."id_User" = c."id_User1"
    JOIN "Profile" u2 ON u2."id_User" = c."id_User2"
    WHERE 
    dm.viewed = 'f' AND dm.deleted = 'f'
    AND (c."id_User1" = $id OR c."id_User2" = $id)
    GROUP BY c.id, u1.id, u2.id;

Edit: grouped the OR clause just to be safe

Upvotes: 4

Related Questions