-1

I need to make a query that selects everything from a table A, and in addition to have a column that indicates the number of times that the value of A.col1 is in B.col2.

Example:

Table A:

id    name
1     "y"
2     "z"
3     "w"

Table B:

id     name
15     "y"
23     "w"
14     "y"

I want a query that will give me the following:

id   name      numOfTimes
1    "y"           2 // y is shown twice in table B
2    "z"           0 // z isn't shown in table B
3    "w"           1 // w is shown once in table B
LoveFortyDown
  • 1,011
  • 2
  • 17
  • 37
Yuvalb
  • 193
  • 1
  • 1
  • 6
  • 1
    Why use Jquery tag for a SQL question ? Did you look at "join" statement ? Or did you look at any SQL tutorial ? – Vincent Decaux Aug 04 '15 at 09:12
  • sorry about the jQuery tag, I've removed it. I've tried to use join, but I had problems. Can you please try to write a query? – Yuvalb Aug 04 '15 at 09:18
  • Use Left Outer join (left table will be table A) and group the results and use count for aggregate. – Kryptonian Aug 04 '15 at 09:19

3 Answers3

2

try this:

 Select a.id, a.name, count(b.id) as numoftimes from a
 left outer join b on a.name = b.name
 group by a.id, a.name;
kl78
  • 1,628
  • 1
  • 16
  • 26
0

It can be done in multiple ways

 select a.id as Id
 ,a.name as Name
 ,(select count(1) from b where a.id=b.id) as NumberOfTimes
 from a;

or

 select a.id as Id
 ,a.name as Name
 ,count(b.id) as NumberOfTimes
 from a
 left join b on a.id=b.id;
Vincent
  • 66
  • 6
0

Try with this following you will get your expected result.

select a.id,a.col1,count(b.col1) as numOfTimes

from TableA a left join TableB b

on a.col1 = b.col1

group by a.id,a.col1;

Thanks.

Phani
  • 93
  • 1
  • 8