join tables to count number of records?

[homerUK]

Hi,

I've two tables

responses
==========
response_id
schema_id
timestamp

answers
==========
answer_id
response_id
answer

(that's a cut down version, but will do for this question)

I need to get all the responses where there is at least one answer in the answers table. But I do not want the answer data. It's literally a quick check for an export to say "get me all the responses where there's at least one question answered".

I have this:

select
r.*
FROM responses r
JOIN task_answers ta on r.response_id = ta.response_id
WHERE r.schema_id = 13
ORDER BY r.response_id ASC

but that's returning all the rows in the answer table. I am not interested in getting those...

.... do I need a LEFT or RIGHT, OUTER or INNER join or something?

cheers for any help!!

[SSJ]

Really Didn't Get This...

[homerUK]

all I need to do is get all the records from TABLEA (responses) where there is at least one record in TABLEB (answers)

[guelphdad]

count the records in table B only, there is no need to do anything with table A. Those questions in table A that haven't been answered won't exist in table B right?

[homerUK]

yeah thats right, I never thought of turning it round like that...

I need to get the records from A though... they are being used... i just want to ignore any records in A that dont have entries in B....

[guelphdad]

An outer join pulls all records from one table and matches up rows from the second table, inserting NULLs where there is no matching record.

select
r.*
FROM responses r
LEFT OUTER JOIN 
task_answers ta 
ON r.response_id = ta.response_id
AND ta.response_id IS NOT NULL
WHERE r.schema_id = 13
ORDER BY r.response_id ASC