CODEWITHSUNDEEP
phpsqljoin
Sam B
Sam B

Reputation: 80

Join is not working properly

I am new to SQL and trying to fetch data using join. Don't know what I am doing wrong. I have two tables.

Table record

recordID|activityName|userID 1 | sale | 5 2 | call | 5 3 | contact | 5

Table activity

activityID|activityData|userID 1 | 50 | 5 2 | 70 | 5 3 | xyz | 5

Result should be

recordID|activityName|activityData|userID 1 | sale | 50 | 5 2 | call | 70 | 5 3 | contact | xyz | 5

But it returns

recordID|activityName|activityData|userID 1 | sale | 50 | 5 2 | call | 70 | 5 3 | contact | xyz | 5 1 | sale | 50 | 5 2 | call | 70 | 5 3 | contact | xyz | 5 1 | sale | 50 | 5 2 | call | 70 | 5 3 | contact | xyz | 5

SELECT * FROM tblrecord tr, tblactivity ta WHERE tr.userID = ta.userID AND tr.userID = '5'

Why is it repeating the result.

PS. userID is the only common field, the activityID and recordID can be different.

Upvotes: 0

Views: 52

Answers (2)

Marcin Pruciak
Marcin Pruciak

Reputation: 366

Add

GROUP BY userID, activityID

to your query.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

First, follow a simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

Second, you seem to want a join on recordId and activityId:

SELECT *
FROM tblrecord tr JOIN
     tblactivity ta 
     ON  tr.userID = ta.userID AND
         tr.recordId = ta.activityId
WHERE tr.userID = 5;

Also, don't use single quotes if the value is really a number.

Upvotes: 3

Related Questions