Loading ...
Sorry, an error occurred while loading the content.
 

Re: [SQLQueriesNoCode] query trouble - help!

Expand Messages
  • Razvan Socol
    Use a LEFT JOIN: SELECT S.[SSN/Student ID], [...], D.Gender, [...] FROM [Scores COMBINED] S LEFT JOIN [Demograph COMBINED] D ON D.Identifier = S.SSN AND
    Message 1 of 3 , Oct 21, 2004
      Use a LEFT JOIN:
       
      SELECT S.[SSN/Student ID], [...], D.Gender, [...]
      FROM [Scores COMBINED] S LEFT JOIN [Demograph COMBINED] D ON D.Identifier = S.SSN
      AND S.[Date OQ45 Taken] = D.[Admin Date]
       
      If you have no corresponding row in the Demographics table, you will get NULLs in those columns, but you will get all the information from the Scores table.
      One more thing: you should be sure that the SSN+Date combination is unique (in both tables), because if it isn't, you'll get more duplicates.
       
      I've also used aliases to make the query slightly more readable.
       
      Razvan

      ----- Original Message -----
      From: Geoff B
      Sent: Thursday, October 21, 2004 10:06 PM
      Subject: [SQLQueriesNoCode] query trouble - help!



      I'm trying to combine two tables for the purpose of research. 
      Unfortunately, the person who put them together didn't do a good job
      of collecting data.  I'm trying to combine a table called Scores with
      a table called Demographics.  The Scores table consists of data for
      each time we saw a test subject.  The Demo table contains test
      subject information.  Ideally, people would've only been entered once
      here, but instead they were entered once for each stage of the test. 
      So, there are 2 or 3 Score entries for each Demo entry.

      I want to keep all the Score records, and add a few fields from the
      Demo table.  But, when I try to query, it does just the opposite.  My
      query is only selecting records from the Score table where there is
      also a record in the Demo table.  If I take out my WHERE clause that
      matches on date, records are paired in such a way that I get 3 times
      as many as I should.  Any thoughts on how I can do this?

      SELECT [Scores COMBINED].[SSN/Student ID], [Scores COMBINED].SSN,
      [Scores COMBINED].[Date OQ45 Taken], [Demograph COMBINED].[Admin
      Date], [Scores COMBINED].[Last Name:], [Scores COMBINED].[First
      Name:], [Demograph COMBINED].Gender, [Scores COMBINED].[Symptom
      Distress], [Scores COMBINED].[Interpersonal Relations], [Scores
      COMBINED].[Social Role], [Scores COMBINED].[Total Score], [Scores
      COMBINED].[Critical Items], [Scores COMBINED].[Initial OQ?], [Scores
      COMBINED].[Termination OQ?], [Demograph COMBINED].[Research Use],
      [Demograph COMBINED].[CMIS Episode #]
      FROM [Demograph COMBINED] INNER JOIN [Scores COMBINED] ON [Demograph
      COMBINED].Identifier = [Scores COMBINED].SSN
      WHERE [Scores COMBINED].[Date OQ45 Taken] =  [Demograph COMBINED].
      [Admin Date];
    • Geoff B
      Thank you! ... D.Identifier = S.SSN ... will get NULLs in those columns, but you will get all the information from the Scores table. ... unique (in both
      Message 2 of 3 , Oct 22, 2004
        Thank you!


        --- In SQLQueriesNoCode@yahoogroups.com, "Razvan Socol" <rsocol@f...>
        wrote:
        > Use a LEFT JOIN:
        >
        > SELECT S.[SSN/Student ID], [...], D.Gender, [...]
        > FROM [Scores COMBINED] S LEFT JOIN [Demograph COMBINED] D ON
        D.Identifier = S.SSN
        > AND S.[Date OQ45 Taken] = D.[Admin Date]
        >
        > If you have no corresponding row in the Demographics table, you
        will get NULLs in those columns, but you will get all the information
        from the Scores table.
        > One more thing: you should be sure that the SSN+Date combination is
        unique (in both tables), because if it isn't, you'll get more
        duplicates.
        >
        > I've also used aliases to make the query slightly more readable.
        >
        > Razvan
        >
        >
        > ----- Original Message -----
        > From: Geoff B
        > To: SQLQueriesNoCode@yahoogroups.com
        > Sent: Thursday, October 21, 2004 10:06 PM
        > Subject: [SQLQueriesNoCode] query trouble - help!
        >
        >
        >
        >
        > I'm trying to combine two tables for the purpose of research.
        > Unfortunately, the person who put them together didn't do a good
        job
        > of collecting data. I'm trying to combine a table called Scores
        with
        > a table called Demographics. The Scores table consists of data
        for
        > each time we saw a test subject. The Demo table contains test
        > subject information. Ideally, people would've only been entered
        once
        > here, but instead they were entered once for each stage of the
        test.
        > So, there are 2 or 3 Score entries for each Demo entry.
        >
        > I want to keep all the Score records, and add a few fields from
        the
        > Demo table. But, when I try to query, it does just the
        opposite. My
        > query is only selecting records from the Score table where there
        is
        > also a record in the Demo table. If I take out my WHERE clause
        that
        > matches on date, records are paired in such a way that I get 3
        times
        > as many as I should. Any thoughts on how I can do this?
        >
        > SELECT [Scores COMBINED].[SSN/Student ID], [Scores COMBINED].SSN,
        > [Scores COMBINED].[Date OQ45 Taken], [Demograph COMBINED].[Admin
        > Date], [Scores COMBINED].[Last Name:], [Scores COMBINED].[First
        > Name:], [Demograph COMBINED].Gender, [Scores COMBINED].[Symptom
        > Distress], [Scores COMBINED].[Interpersonal Relations], [Scores
        > COMBINED].[Social Role], [Scores COMBINED].[Total Score], [Scores
        > COMBINED].[Critical Items], [Scores COMBINED].[Initial OQ?],
        [Scores
        > COMBINED].[Termination OQ?], [Demograph COMBINED].[Research Use],
        > [Demograph COMBINED].[CMIS Episode #]
        > FROM [Demograph COMBINED] INNER JOIN [Scores COMBINED] ON
        [Demograph
        > COMBINED].Identifier = [Scores COMBINED].SSN
        > WHERE [Scores COMBINED].[Date OQ45 Taken] = [Demograph COMBINED].
        > [Admin Date];
      Your message has been successfully submitted and would be delivered to recipients shortly.