Avoiding Cartesian Product with Inner Joins

Question

I know this has been asked before, but I'm having difficulty in grasping it. I rarely use raw SQL.
I have three tables - Session, GroupName and GroupSessionFeedback.

I'm joining Session to GroupFeedback on idUser and I'm joining GroupName to Session on idGroup column.

Here's my SQL code:

SELECT s.idSession, 
       g.name, 
       s.Assistant, 
       s.idGroup, 
       s.start, 
       s.end, 
       f.value
  FROM rempad.Session s
 INNER JOIN rempad.GroupSessionFeedback f
    ON s.idUser = f.idUser
 INNER JOIN rempad.GroupName g
    ON s.idGroup = g.idGroup
 WHERE s.start BETWEEN '2013-04-28' AND '2013-05-28'
   AND s.idUser = 22
    OR s.idUser = 23
    OR s.idUser = 24
    OR s.idUser = 26
    OR s.idUser = 27
    OR s.idUser = 28
    OR s.idUser = 42;

I want it to bring back unique idSessions but it is matching it multiple times because of the Joins and I really haven't an idea what other approach to take. I could make separate calls to the database - but I'm really trying to avoid hammering the database as it's pretty slow response as it is.

Any ideas?

Thanks in advance

Two points: you are mixing `ands` and `ors`. `And` has precedence over `or`, resulting in `start` filter being honored for iduser 22 only. Other users are simply appended to result. See [Mysql or/and precedence?](http://stackoverflow.com/questions/12345569/mysql-or-and-precedence). Use `in` or enclose all the `ors` in parentheses. Second point: if performance does not improve then you probably need to revisit indexes. — Nikola Markovinović, Aug 31 '13 at 18:45

score 4 · Answer 1 · edited May 23 '17 at 12:24

I used the example on this page : SQL query returning cartesian product to figure it out. In fairness I did not supply the database structure in my question. But the problem was that in each of the tables that I was joining there were more than one column that matched so I needed to reflect that in my Join statments. Here is a sample

 INNER JOIN rempad.GroupSessionFeedback f
 ON s.idUser = f.idUser 
 AND s.idSession = f.idSession 
 AND s.idGroup = f.idGroup

I hope this helps someone else. L

score 0 · Answer 2 · answered Aug 31 '13 at 18:03

0

Try using this:

select {.......}
from session s inner join groupsessionfeedback f inner join groupname g
on s.iduser = f.iduser and s.idgroup = g.idgroup
where {.......}

I hope this solves it. However if any other filters are needed in this query, tell me.

answered Aug 31 '13 at 18:03

Rijul

515
7
19

Hi Thanks for your response, but I believe that your solution is still producing the same results. It's just two innerjoins written with another syntax, but fundamentally it will still produce a Cartesian product of the tables. This is what I am hoping to avoid. But thank you for your answer – Linda Keating Aug 31 '13 at 18:09

score 0 · Answer 3 · answered Aug 31 '13 at 19:24

Does this version of the query suffer the same problem:

SELECT s.idSession, g.name, s.Assistant, s.idGroup, s.start, s.end, f.value
  FROM rempad.Session s
 INNER JOIN rempad.GroupSessionFeedback f
    ON s.idUser = f.idUser
 INNER JOIN rempad.GroupName g
    ON s.idGroup = g.idGroup
 WHERE s.start BETWEEN '2013-04-28' AND '2013-05-28'
   AND s.idUser in (22, 23, 24, 26, 27, 28, 42);

If it is still producing duplicate results, then what do you want the rows to be? After all, this will fix the problem with multiple idsession, but it doesn't return g.name or f.value:

SELECT distinct s.idSession, s.Assistant, s.idGroup, s.start, s.end
  FROM rempad.Session s
 INNER JOIN rempad.GroupSessionFeedback f
    ON s.idUser = f.idUser
 INNER JOIN rempad.GroupName g
    ON s.idGroup = g.idGroup
 WHERE s.start BETWEEN '2013-04-28' AND '2013-05-28'
   AND s.idUser in (22, 23, 24, 26, 27, 28, 42);

Avoiding Cartesian Product with Inner Joins

3 Answers3