...

View Full Version : join tables to count number of records?



homerUK
03-29-2007, 11:25 AM
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
03-29-2007, 11:43 AM
Really Didn't Get This...

homerUK
03-29-2007, 01:14 PM
all I need to do is get all the records from TABLEA (responses) where there is at least one record in TABLEB (answers)

guelphdad
03-29-2007, 02:34 PM
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
03-29-2007, 02:36 PM
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
03-29-2007, 02:39 PM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum