Select statment based on child rows

[grhodes29]

Table USERS

ID(PK) FNAME
1 Joe
2 Tom
3 Frank
4 John


Table OBSERATIONS


ID(PK) USERS_ID(FK) SCORE YEAR
1 2 345 2007
2 2 244 2008
3 2 232 2008
4 1 234 2007
5 1 233 2007
6 1 232 2008
7 1 345 2008
8 1 234 2009
9 3 123 2007
10 3 344 2009
11 4 123 2007
12 4 234 2008
13 4 234 2009
14 4 224 2009


I want to create a SELECT statement that relates these two tables together on
ID/USERS_ID and
I want only the USERS where they have related rows for 2007,2008 and 2009.
The users in the return set must have OBSERVATIONS for all three years.

My return set should be something like

ID FNAME
1 Joe
2 John

What is ths select syntax to use? I can't seemt to get in quite right. Thanks.

 

[Bob Barrows]

Table USERS

ID(PK) FNAME
1 Joe
2 Tom
3 Frank
4 John


Table OBSERATIONS


ID(PK) USERS_ID(FK) SCORE YEAR

You should consider renaming that "YEAR" field. "Year" is the name of a
VBA function and therefore is a reserved keyword. I would suggest
something like "ScoreYear" which not only avoids the reserved-keyword
problem but is also more descriptive.

1 2 345 2007
2 2 244 2008
3 2 232 2008
4 1 234 2007
5 1 233 2007
6 1 232 2008
7 1 345 2008
8 1 234 2009
9 3 123 2007
10 3 344 2009
11 4 123 2007
12 4 234 2008
13 4 234 2009
14 4 224 2009


I want to create a SELECT statement that relates these two tables
together on ID/USERS_ID and
I want only the USERS where they have related rows for 2007,2008 and
2009. The users in the return set must have OBSERVATIONS for all
three years.

My return set should be something like

ID FNAME
1 Joe
2 John

What is ths select syntax to use? I can't seemt to get in quite
right. Thanks.

There are several ways to accomplish this. One way is to create a saved
query on the Observations table using the Crosstab query wizard, using
users_id for the rows, ScoreYear for the columns, and Score for the
aggregated value (I would probably use the Count aggregation).

Then, in a separate query, join the Users table with the saved crosstab
query and enter >0 in the criteria row under each of the year colums.

 

[MGFoster]

Table USERS

ID(PK) FNAME
1 Joe
2 Tom
3 Frank
4 John


Table OBSERATIONS


ID(PK) USERS_ID(FK) SCORE YEAR
1 2 345 2007
2 2 244 2008
3 2 232 2008
4 1 234 2007
5 1 233 2007
6 1 232 2008
7 1 345 2008
8 1 234 2009
9 3 123 2007
10 3 344 2009
11 4 123 2007
12 4 234 2008
13 4 234 2009
14 4 224 2009


I want to create a SELECT statement that relates these two tables together on
ID/USERS_ID and
I want only the USERS where they have related rows for 2007,2008 and 2009.
The users in the return set must have OBSERVATIONS for all three years.

My return set should be something like

ID FNAME
1 Joe
2 John

What is ths select syntax to use? I can't seemt to get in quite right. Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this:

SELECT U.ID, U.FNAME
FROM Users As U INNER JOIN (SELECT USERS_ID, [YEAR] FROM Observations
GROUP BY USERS_ID, [YEAR]) AS O ON U.ID = O.USERS_ID
WHERE O.[YEAR] IN (2007, 2008, 2009)
GROUP BY U.ID, U.FNAME
HAVING COUNT(*)=3

If you want more, or less, years change the COUNT(*)= ? and the
IN (years) phrases.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUEOoechKqOuFEgEQK8ywCfWoJy4wSI8jDlbRIyLM+vDPj+vh4Anj7H
FqRlxXaz+elEPHO/zu1s+rKY
=Y+sq
-----END PGP SIGNATURE-----

 

[KARL DEWEY]

Try this --
SELECT USERS.FNAME
FROM USERS INNER JOIN ((OBSERATIONS INNER JOIN OBSERATIONS AS OBSERATIONS_1
ON OBSERATIONS.USERS_ID = OBSERATIONS_1.USERS_ID) INNER JOIN OBSERATIONS AS
OBSERATIONS_2 ON OBSERATIONS.USERS_ID = OBSERATIONS_2.USERS_ID) ON USERS.ID =
OBSERATIONS.USERS_ID
WHERE (((OBSERATIONS.ScoreYear)=2007) AND ((OBSERATIONS_1.ScoreYear)=2008)
AND ((OBSERATIONS_2.ScoreYear)=2009))
GROUP BY USERS.FNAME;

 

[KARL DEWEY]

grhodes29 has duplicate years like User 2 - 2007, 2008, and 2008. I think
your query would result in count of three and therefore an error.

Table USERS

ID(PK) FNAME
1 Joe
2 Tom
3 Frank
4 John


Table OBSERATIONS


ID(PK) USERS_ID(FK) SCORE YEAR
1 2 345 2007
2 2 244 2008
3 2 232 2008
4 1 234 2007
5 1 233 2007
6 1 232 2008
7 1 345 2008
8 1 234 2009
9 3 123 2007
10 3 344 2009
11 4 123 2007
12 4 234 2008
13 4 234 2009
14 4 224 2009


I want to create a SELECT statement that relates these two tables together on
ID/USERS_ID and
I want only the USERS where they have related rows for 2007,2008 and 2009.
The users in the return set must have OBSERVATIONS for all three years.

My return set should be something like

ID FNAME
1 Joe
2 John

What is ths select syntax to use? I can't seemt to get in quite right. Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this:

SELECT U.ID, U.FNAME
FROM Users As U INNER JOIN (SELECT USERS_ID, [YEAR] FROM Observations
GROUP BY USERS_ID, [YEAR]) AS O ON U.ID = O.USERS_ID
WHERE O.[YEAR] IN (2007, 2008, 2009)
GROUP BY U.ID, U.FNAME
HAVING COUNT(*)=3

If you want more, or less, years change the COUNT(*)= ? and the
IN (years) phrases.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUEOoechKqOuFEgEQK8ywCfWoJy4wSI8jDlbRIyLM+vDPj+vh4Anj7H
FqRlxXaz+elEPHO/zu1s+rKY
=Y+sq
-----END PGP SIGNATURE-----

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I tested it w/ the data grhodes29 provided & got Joe (1) & John (4) -
the only users who have all three years, even if some are duplicates.
The derived table groups by user_id & year, which groups the distinct
years together per user_id.

Regards,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUU84echKqOuFEgEQLzDwCeOPP/TeAQpv62VYej2YRFMzwdPKAAoJqQ
lJ00ydMPFau/snp3muMDoOuH
=zhJe
-----END PGP SIGNATURE-----

grhodes29 has duplicate years like User 2 - 2007, 2008, and 2008. I think
your query would result in count of three and therefore an error.

Table USERS

ID(PK) FNAME
1 Joe
2 Tom
3 Frank
4 John


Table OBSERATIONS


ID(PK) USERS_ID(FK) SCORE YEAR
1 2 345 2007
2 2 244 2008
3 2 232 2008
4 1 234 2007
5 1 233 2007
6 1 232 2008
7 1 345 2008
8 1 234 2009
9 3 123 2007
10 3 344 2009
11 4 123 2007
12 4 234 2008
13 4 234 2009
14 4 224 2009


I want to create a SELECT statement that relates these two tables together on
ID/USERS_ID and
I want only the USERS where they have related rows for 2007,2008 and 2009.
The users in the return set must have OBSERVATIONS for all three years.

My return set should be something like

ID FNAME
1 Joe
2 John

What is ths select syntax to use? I can't seemt to get in quite right. Thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this:

SELECT U.ID, U.FNAME
FROM Users As U INNER JOIN (SELECT USERS_ID, [YEAR] FROM Observations
GROUP BY USERS_ID, [YEAR]) AS O ON U.ID = O.USERS_ID
WHERE O.[YEAR] IN (2007, 2008, 2009)
GROUP BY U.ID, U.FNAME
HAVING COUNT(*)=3

If you want more, or less, years change the COUNT(*)= ? and the
IN (years) phrases.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUEOoechKqOuFEgEQK8ywCfWoJy4wSI8jDlbRIyLM+vDPj+vh4Anj7H
FqRlxXaz+elEPHO/zu1s+rKY
=Y+sq
-----END PGP SIGNATURE-----

 

[KARL DEWEY]

I did not look close enough.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I tested it w/ the data grhodes29 provided & got Joe (1) & John (4) -
the only users who have all three years, even if some are duplicates.
The derived table groups by user_id & year, which groups the distinct
years together per user_id.

Regards,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUU84echKqOuFEgEQLzDwCeOPP/TeAQpv62VYej2YRFMzwdPKAAoJqQ
lJ00ydMPFau/snp3muMDoOuH
=zhJe
-----END PGP SIGNATURE-----

grhodes29 has duplicate years like User 2 - 2007, 2008, and 2008. I think
your query would result in count of three and therefore an error.

grhodes29 wrote:
Table USERS

ID(PK) FNAME
1 Joe
2 Tom
3 Frank
4 John


Table OBSERATIONS


ID(PK) USERS_ID(FK) SCORE YEAR
1 2 345 2007
2 2 244 2008
3 2 232 2008
4 1 234 2007
5 1 233 2007
6 1 232 2008
7 1 345 2008
8 1 234 2009
9 3 123 2007
10 3 344 2009
11 4 123 2007
12 4 234 2008
13 4 234 2009
14 4 224 2009


I want to create a SELECT statement that relates these two tables together on
ID/USERS_ID and
I want only the USERS where they have related rows for 2007,2008 and 2009.
The users in the return set must have OBSERVATIONS for all three years.

My return set should be something like

ID FNAME
1 Joe
2 John

What is ths select syntax to use? I can't seemt to get in quite right. Thanks.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this:

SELECT U.ID, U.FNAME
FROM Users As U INNER JOIN (SELECT USERS_ID, [YEAR] FROM Observations
GROUP BY USERS_ID, [YEAR]) AS O ON U.ID = O.USERS_ID
WHERE O.[YEAR] IN (2007, 2008, 2009)
GROUP BY U.ID, U.FNAME
HAVING COUNT(*)=3

If you want more, or less, years change the COUNT(*)= ? and the
IN (years) phrases.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUEOoechKqOuFEgEQK8ywCfWoJy4wSI8jDlbRIyLM+vDPj+vh4Anj7H
FqRlxXaz+elEPHO/zu1s+rKY
=Y+sq
-----END PGP SIGNATURE-----