x

getting counts from relational tables on join

looking for some help here as i am getting frustrated, I have a table that has a few relational database. Lets say the head table is called person, each person can have multiple tests, and each one of those tests can have multiple questions. What i want to check is that each person has filled has at least one entry in the test table and that there is at least on entry in the questions table for a test. I just need to know has a person filled out at least on test and there are questions tied to a test that they have filled out.
more ▼

asked May 26, 2011 at 04:19 PM in Default

klandathu gravatar image

klandathu
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest
Can you tell us a bit about the tables? The relationship between them. As many details as you can give will help us greatly.
more ▼

answered May 26, 2011 at 04:22 PM

Tim gravatar image

Tim
36.4k 36 41 139

so table person is just your basic table it has an auto num which we will call person id, name ect ect. Table Tests joins on the person table with a fk of person id. it also has a auto generated "test id" as its pk. Questions joins on test via a fk with Test ID. I can provide actual structure if needed
May 26, 2011 at 04:44 PM klandathu
SELECT DISTINCT P.person_id, count(t.person_id), count(q.test_id)
FROM person P
JOIN test T on P.person_id = T.person_id
JOIN questions Q on T.test_id = Q.test_id
GROUP BY P.person_id
May 26, 2011 at 04:51 PM Tim
that would work but i am doing some case statements and logic in the select statement that i dont think i can group by. i could possible rewrite the sp to insert those values into a temp table and then join. is there a way to do this withouth grouping?
May 26, 2011 at 05:00 PM klandathu
Can you paste your code? That would help me help you.
May 26, 2011 at 05:02 PM Tim
@TRAD without reading the comments, I was thinking, you may be the first to have answered a question with a question--and it being accepted! :)
May 26, 2011 at 09:03 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948

asked: May 26, 2011 at 04:19 PM

Seen: 1298 times

Last Updated: May 26, 2011 at 04:19 PM