We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

SQL Query Help

Hey Folks -
I'm sure I'm going to get plenty of, "You're doing things fundamentally wrong". That's fine. But, I have need to write a query that returns all the questions and responses for a particular subject, event, and section. I've been trying to figure out how to write such a query, but am not having much luck. Does anybody have a query laying around that will do this? Or is there some documentation I'm over looking that will point me in the right direction?
Thanks!
-Brian

Comments

  • bbuijingbbuijing Posts: 7
    Hi Brian,
    I've been fooling around a bit with the datamodel last week and came up with the query attached.
    By adding some where clauses you can filter events and subjects. It's my first go at it and not thoroughly tested, but it might help.
    Regards,
    Bjorn Buijing
    -----Original Message-----
    Sent: dinsdag 16 oktober 2012 10:58
    To: [email protected]
    Subject: [Users] SQL Query Help
    Hey Folks -
    I'm sure I'm going to get plenty of, "You're doing things fundamentally wrong". That's fine. But, I have need to write a query that returns all the questions and responses for a particular subject, event, and section. I've been trying to figure out how to write such a query, but am not having much luck. Does anybody have a query laying around that will do this? Or is there some documentation I'm over looking that will point me in the right direction?
    Thanks!
    -Brian
  • zwetszwets Posts: 59
    Bjorn,
    On 16 Oct 2012, at 13:01, B.R. Buijing wrote:
    > >
    This is pretty much the query I would have come up with. Note that you can write the JOIN statements more succinctly using JOIN ... USING, since OC uses identical PK/FK names for most relations. E.g.: item_data id JOIN item i USING (item_id) ... etc.
    The FROM clause could then be like this (indentation to show 'degrees of separation'):
    item_data id
    JOIN item i
    USING (item_id)
    JOIN event_crf ec
    JOIN study_subject ss
    USING (study_subject_id)
    JOIN study_event se
    JOIN study_event_definition sed
    JOIN study s
    USING (study_id)
    USING (study_event_definition_id)
    USING (study_event_id)
    USING (event_crf_id)
    ...
    Best regards,
    Marco
  • ServerSageServerSage Posts: 32
    Thank you VERY much Bjorn, you filled in the gaps I wasn't able to! And with the suggestions from Marco I am in business, much appreciated!
    -Brian
    On Oct 16, 2012, at 3:01 AM, B.R. Buijing wrote:
    > > Hi Brian,
    > >
    > > I've been fooling around a bit with the datamodel last week and came up with the query attached.
    > > By adding some where clauses you can filter events and subjects. It's my first go at it and not thoroughly tested, but it might help.
    > >
    > > Regards,
    > > Bjorn Buijing
    > >
    > >
    > >
    > > -----Original Message-----
    > >
    > > Sent: dinsdag 16 oktober 2012 10:58
    > > To: [email protected]
    > > Subject: [Users] SQL Query Help
    > >
    > > Hey Folks -
    > >
    > > I'm sure I'm going to get plenty of, "You're doing things fundamentally wrong". That's fine. But, I have need to write a query that returns all the questions and responses for a particular subject, event, and section. I've been trying to figure out how to write such a query, but am not having much luck. Does anybody have a query laying around that will do this? Or is there some documentation I'm over looking that will point me in the right direction?
    > >
    > > Thanks!
    > >
    > > -Brian
    > >
    > >
  • zwetszwets Posts: 59
    Brian, all,
    On 17 Oct 2012, at 10:15, Brian Johnson wrote:
    > Thank you VERY much Bjorn, you filled in the gaps I wasn't able to! And with the suggestions from Marco I am in business, much appreciated!
    See the attached snapshot of the whiteboard in my office, it is my reference for writing the JOINs in queries. Arrows are foreign-key references. Stars indicate n-cardinality. I have omitted the n:n maps to DN and between CRFV and I. Caveat emptor.
    Another tip: typing \d tablename in psql shows the incoming and outgoing foreign-key-constraints for tablename. It has tab-completion.
    Best regards,
    Marco
  • ccollinsccollins Posts: 379 admin
    Hey Marco,
    This is a neat reference. Mind if we put it up on the technical docs site (with citation, of course)?
    Thanks,
    Cal
    On Wednesday, October 17, 2012, Marco van Zwetselaar wrote:
    Brian, all,
    On 17 Oct 2012, at 10:15, Brian Johnson wrote:
    > Thank you VERY much Bjorn, you filled in the gaps I wasn't able to! And with the suggestions from Marco I am in business, much appreciated!
    See the attached snapshot of the whiteboard in my office, it is my reference for writing the JOINs in queries. Arrows are foreign-key references. Stars indicate n-cardinality. I have omitted the n:n maps to DN and between CRFV and I. Caveat emptor.
    Another tip: typing \d tablename in psql shows the incoming and outgoing foreign-key-constraints for tablename. It has tab-completion.
    Best regards,
    Marco
    --
    -------------------------------------------------------------------
    Cal Collins
    OpenClinica LLC
    781.547.8411
    [email protected]
    Professional Open Source for Clinical Research
    www.OpenClinica.com
  • GerbenRienkGerbenRienk Posts: 814 ✭✭✭
    Dear All,

    This thread reminded me of the OC-pre-version-3 days. There was a nice clickable database schema, still available at
    https://community.openclinica.com/OpenClinica/3.0/technical/DataModel_3.0/index.htm
    but that was generated in 2006 (although a lot still stands).
    Fortunately there are (many) OpenSource utilities that can generate a graphical representation of your db and you can view the result of SchemaSpy on an OpenClinica 3.1.2 database on
    http://trialdatasolutions.com/tds/howto/schemaspy.jsp
    Hope this helps,

    Gerben Rienk


    Van: [email protected] [mailto:[email protected]] Namens Marco van Zwetselaar
    Verzonden: woensdag 17 oktober 2012 11:01
    Aan: [email protected]
    Onderwerp: Re: [Users] SQL Query Help

    Brian, all,

    On 17 Oct 2012, at 10:15, Brian Johnson wrote:
    Thank you VERY much Bjorn, you filled in the gaps I wasn't able to! And with the suggestions from Marco I am in business, much appreciated!

    See the attached snapshot of the whiteboard in my office, it is my reference for writing the JOINs in queries. Arrows are foreign-key references. Stars indicate n-cardinality. I have omitted the n:n maps to DN and between CRFV and I. Caveat emptor.

    Another tip: typing \d tablename in psql shows the incoming and outgoing foreign-key-constraints for tablename. It has tab-completion.

    Best regards,
    Marco
  • ccollinsccollins Posts: 379 admin
    Hi All,
    A diagram of the current database schema is here:
    https://dev.openclinica.com/tools/db/relationships.html
    We'll shortly be adding a page to the documentation site with a link to this diagram plus Marco's "cheat sheet".
    Cal
    On Saturday, October 20, 2012, Gerben Rienk wrote:
    Dear All,

    This thread reminded me of the OC-pre-version-3 days. There was a nice clickable database schema, still available at
    https://community.openclinica.com/OpenClinica/3.0/technical/DataModel_3.0/index.htm
    but that was generated in 2006 (although a lot still stands).
    Fortunately there are (many) OpenSource utilities that can generate a graphical representation of your db and you can view the result of SchemaSpy on an OpenClinica 3.1.2 database on
    http://trialdatasolutions.com/tds/howto/schemaspy.jsp
    Hope this helps,

    Gerben Rienk


    Van: [email protected] [mailto:[email protected]] Namens Marco van Zwetselaar
    Verzonden: woensdag 17 oktober 2012 11:01
    Aan: [email protected]
    Onderwerp: Re: [Users] SQL Query Help

    Brian, all,

    On 17 Oct 2012, at 10:15, Brian Johnson wrote:
    Thank you VERY much Bjorn, you filled in the gaps I wasn't able to! And with the suggestions from Marco I am in business, much appreciated!

    See the attached snapshot of the whiteboard in my office, it is my reference for writing the JOINs in queries. Arrows are foreign-key references. Stars indicate n-cardinality. I have omitted the n:n maps to DN and between CRFV and I. Caveat emptor.

    Another tip: typing \d tablename in psql shows the incoming and outgoing foreign-key-constraints for tablename. It has tab-completion.

    Best regards,
    Marco




    --
    -------------------------------------------------------------------
    Cal Collins
    OpenClinica LLC
    781.547.8411
    [email protected]
    Professional Open Source for Clinical Research
    www.OpenClinica.com
This discussion has been closed.