Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post

    join tables to count number of records?

    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:

    Code:
    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!!

  • #2
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Really Didn't Get This...

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    all I need to do is get all the records from TABLEA (responses) where there is at least one record in TABLEB (answers)

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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?

  • #5
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    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....

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

    Code:
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •