CODEWITHSUNDEEP
sqlsyntaxcartesian-product
Linda Keating
Linda Keating

Reputation: 2435

Avoiding Cartesian Product with Inner Joins

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

Upvotes: 3

Views: 18373

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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);

Upvotes: 0

Linda Keating
Linda Keating

Reputation: 2435

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

Upvotes: 4

Rijul
Rijul

Reputation: 515

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.

Upvotes: 0

Related Questions