Count lines in select

[Sam]

I have a table t1:

id int unique
added timestamp

and second table t2:

eid int
id int unique

t2.id are IDs from t1. t2.eid with the same value can be found in t2
more than for one entry but they will all have different IDs.

I want to:

find all EID that have IDs added after some DATE. Result should count
how many of these IDs are there for the same EID. Result should be of
the form (this is example):

EID | counts
-------+-----------
39 | 3
45 | 1

which means that EID 39 has 3 IDs in t1 that were added after DATE and
45 has only 1.

How to do that?

[toby]

On Nov 14, 11:41 am, Sam <ksam...@gmail.com> wrote:
> I have a table t1:
>
> id        int unique
> added timestamp
>
> and second table t2:
>
> eid  int
> id    int unique
>
> t2.id are IDs from t1. t2.eid with the same value can be found in t2
> more than for one entry but they will all have different IDs.
>
> I want to:
>
> find all EID that have IDs added after some DATE. Result should count
> how many of these IDs are there for the same EID.

SELECT eid, COUNT(*)
FROM t2 LEFT JOIN t1 USING(id)
WHERE added > d
GROUP BY eid

[Jerry Stuckle]

Unless I'm missing something, this should just be a simple JOIN with a
GROUP BY clause:

SELECT t1.id, count(t2)
FROM t1
JOIN t2 ON t1.id = t2.eid
WHERE t1.`timestamp` < (desired timestamp value here)
GROUP BY t1.id

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================