CODEWITHSUNDEEP
mysqlsqljoincount
handsome
handsome

Reputation: 2432

using JOIN in the same table and COUNT

I have this table

tracking

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.

Upvotes: 0

Views: 111

Answers (1)

Travesty3
Travesty3

Reputation: 14489

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

Upvotes: 2

Related Questions