join query in mysql

Question

i am having table structure like this

   table1       table2

    pid  pname       pid uid  cat
    1       a          1   1   1
    2       b          1   2   1
    3       c          1   3   1

select * from table1 as t1 LEFT JOIN table2 as t2 on t1.pid=t2.pid where t2.uid=1 AND t2.cat=1

it's select the two rows

i don't want to group pid because i may have same pid in table one so i need to get only number of rows matched with pid

This may be silly question but i tried hard i couldn't get anything .

I hope you people can help me!

Thanks in advance

Answer 1

If you want to get the count of rows that match:

SELECT COUNT(*)
FROM table1 t1 INNER JOIN table2 t2 on t1.pid = t2.pid
WHERE t2.uid=1

Your LEFT join is unnecessary, since you are filtering by table2.

http://en.wikipedia.org/wiki/Join_%28SQL%29