0

I have a simple question on how to read the number of occurences of some values in a table B that references to a value in a table A. It's all explained better in the following example:

Let's say I have two simple tables, A with an attribute ID and B with an attribute ID that references A.ID; I use this code to find the number of occurences of a A.ID value into B:

SELECT A.ID, COUNT(*)
FROM A JOIN B ON A.ID = B.ID
GROUP BY A.ID

Is it possible to achieve the same result using something like the following code...

SELECT ID, -- SOMETHING --
FROM A
....

... using this subquery?

SELECT COUNT(*)
FROM B WHERE B.ID = A.ID

Thank you very much

rickyalbert
  • 2,552
  • 4
  • 21
  • 31

1 Answers1

2

I think you might be referring to a correlated subquery:

select a.id, (select count(1) from b where id=a.id) cnt from a;

The a.id term in the subquery binds with table a in the outer query.

bgoldst
  • 34,190
  • 6
  • 38
  • 64
  • That's exactly what I'm talking about, thank you! Can I ask you what is count(1) ? Is that related to the fact that the inner query must return 1 result? – rickyalbert Nov 29 '14 at 19:29
  • 1
    See http://stackoverflow.com/questions/1221559/count-vs-count1. Basically, there's no difference; it's a matter of preference. I prefer count(1) because the lexical context inside the aggregate function can really take any expression, and 1 is a valid expression, but * is not. The count(*) form is sort of a special form that serves no purpose. But either form works. – bgoldst Nov 29 '14 at 19:34