using JOIN in the same table and COUNT

Question

I have this table

tracking

id
referer
status
session

i want to COUNT rows for the two tables (joined with itself)

SELECT COUNT(t1.id), COUNT(t2.id)
FROM tracking t1
INNER JOIN tracking t2 on t2.session = t1.session AND t2.status = 2
WHERE t1.referer = 'http://google.com' AND t1.status = 1

with this data :

id | referer | status | session

1 | http://google.com | 1 | ABC

2 | ################# | 2 | ABC

i need to get (1,1) but im getting (1,null)

i tried with RIGHT JOIN but is not working either.

i added a new record to your sql fiddle (3, 'google.com', 1, 'CBA') and it still returns 1,1 and should be 2,1 (my initial question need to be updated) sqlfiddle.com/#!9/e3bda/1/0 — handsome
– handsome, Commented Nov 14, 2013 at 2:59
What are you trying to achieve? What is the task this code is supposed to perform? INNER JOIN can't possibly return different counts since you're filtering out any possible NULLs, so every row is getting counted for both fields. — PM 77-1
– PM 77-1, Commented Nov 14, 2013 at 3:20

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Use LEFT JOIN instead of INNER JOIN:

Example

SELECT COUNT(t1.id), COUNT(t2.id)
FROM tracking t1
LEFT JOIN tracking t2 on t2.session = t1.session AND t2.status = 2
WHERE t1.referer = 'http://google.com' AND t1.status = 1;

t1 is the "left" table and t2 is the "right" table. You want to use LEFT JOIN to make sure you get all the rows from the left table (t1), regardless of whether or not there is a match in the right table (t2).

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 14, 2013 at 3:19

Travesty3

14.5k7 gold badges64 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using JOIN in the same table and COUNT

1 Answer 1

Example

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related